Please help with db query
Hi,
I'm trying to get data from a db query on an external excel file so I recorded the following macro. I then tried to pass variables for the path and filename (instead of where it had C:\mydocuments etc etc) and cannot get it to work. I get an ODBC Excel Driver Login Failed message - Failure Creating File. When I click OK on this message the correct filename is shown in the database name box, however it has not moved to the correct path. I'd appreciate any help. Sub getdata() Worksheets("accounts").Range("b3:Z20000").ClearCon tents Dim filname As String Dim shtname As String Dim path As String Dim PATHNAME As String shtname = Sheets("details").Range("c2").Value filname = Sheets("details").Range("c2").Value & ".xls" path = Sheets("details").Range("u1").Value PATHNAME = Sheets("details").Range("u1").Value & "\" & filname & ";" Debug.Print PATHNAME Debug.Print path Sheets("sheet1").Activate ActiveSheet.Range("a1").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=Excel Files;DBQ='" & PATHNAME & path & ";" _ ), Array("DriverId=790;MaxBufferSize=2048;PageTimeout =5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT `South$`.`BDE Territory`, `South$`.County, `South $`.Division, `South$`.`Inner Postcode`, `South$`.`Last BDE Visit`, `South$`.Locality, `South$`.Operator, `South$`.`Outer Postcode`, `South $`.Out" _ , _ "let, `South$`.`Outlet Status`, `South$`.Owner, `South $`.`Phone No#`, `South$`.`Primary Streetmap`, `South$`.Region, `South $`.`Siebel Id`, `South$`.`Street Address`, `South$`.Tenure, `South $`.Town" & Chr(13) & "" & Chr(10) & "FRO" _ , _ "M `C:\Documents and Settings\DowsonKe\Desktop\South`.`South$` `South$`" & Chr(13) & "" & Chr(10) & "WHERE (`South$`.`BDE Territory`='BDE0804')" _ ) .Name = "Query from Excel Files_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
Please help with db query
Lets see you recorded macro
"keri" wrote: Hi, I'm trying to get data from a db query on an external excel file so I recorded the following macro. I then tried to pass variables for the path and filename (instead of where it had C:\mydocuments etc etc) and cannot get it to work. I get an ODBC Excel Driver Login Failed message - Failure Creating File. When I click OK on this message the correct filename is shown in the database name box, however it has not moved to the correct path. I'd appreciate any help. Sub getdata() Worksheets("accounts").Range("b3:Z20000").ClearCon tents Dim filname As String Dim shtname As String Dim path As String Dim PATHNAME As String shtname = Sheets("details").Range("c2").Value filname = Sheets("details").Range("c2").Value & ".xls" path = Sheets("details").Range("u1").Value PATHNAME = Sheets("details").Range("u1").Value & "\" & filname & ";" Debug.Print PATHNAME Debug.Print path Sheets("sheet1").Activate ActiveSheet.Range("a1").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=Excel Files;DBQ='" & PATHNAME & path & ";" _ ), Array("DriverId=790;MaxBufferSize=2048;PageTimeout =5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT `South$`.`BDE Territory`, `South$`.County, `South $`.Division, `South$`.`Inner Postcode`, `South$`.`Last BDE Visit`, `South$`.Locality, `South$`.Operator, `South$`.`Outer Postcode`, `South $`.Out" _ , _ "let, `South$`.`Outlet Status`, `South$`.Owner, `South $`.`Phone No#`, `South$`.`Primary Streetmap`, `South$`.Region, `South $`.`Siebel Id`, `South$`.`Street Address`, `South$`.Tenure, `South $`.Town" & Chr(13) & "" & Chr(10) & "FRO" _ , _ "M `C:\Documents and Settings\DowsonKe\Desktop\South`.`South$` `South$`" & Chr(13) & "" & Chr(10) & "WHERE (`South$`.`BDE Territory`='BDE0804')" _ ) .Name = "Query from Excel Files_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
Please help with db query
Keri,
One way to get/test connection strings is: - Create a empty text file somewhere suitable. - Rename it "Whatever.udl". - Double click it. Follow the wizard, making the various setting. - Test connection. If successful, click OK. - Open the file in a text editor and copy the connection string. You can use .udl files in queries also, but I prefer to copy the connection string from them instead. NickHK "keri" wrote in message ups.com... Hi, I'm trying to get data from a db query on an external excel file so I recorded the following macro. I then tried to pass variables for the path and filename (instead of where it had C:\mydocuments etc etc) and cannot get it to work. I get an ODBC Excel Driver Login Failed message - Failure Creating File. When I click OK on this message the correct filename is shown in the database name box, however it has not moved to the correct path. I'd appreciate any help. Sub getdata() Worksheets("accounts").Range("b3:Z20000").ClearCon tents Dim filname As String Dim shtname As String Dim path As String Dim PATHNAME As String shtname = Sheets("details").Range("c2").Value filname = Sheets("details").Range("c2").Value & ".xls" path = Sheets("details").Range("u1").Value PATHNAME = Sheets("details").Range("u1").Value & "\" & filname & ";" Debug.Print PATHNAME Debug.Print path Sheets("sheet1").Activate ActiveSheet.Range("a1").Select With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=Excel Files;DBQ='" & PATHNAME & path & ";" _ ), Array("DriverId=790;MaxBufferSize=2048;PageTimeout =5;")), Destination:=Range("A1")) .CommandText = Array( _ "SELECT `South$`.`BDE Territory`, `South$`.County, `South $`.Division, `South$`.`Inner Postcode`, `South$`.`Last BDE Visit`, `South$`.Locality, `South$`.Operator, `South$`.`Outer Postcode`, `South $`.Out" _ , _ "let, `South$`.`Outlet Status`, `South$`.Owner, `South $`.`Phone No#`, `South$`.`Primary Streetmap`, `South$`.Region, `South $`.`Siebel Id`, `South$`.`Street Address`, `South$`.Tenure, `South $`.Town" & Chr(13) & "" & Chr(10) & "FRO" _ , _ "M `C:\Documents and Settings\DowsonKe\Desktop\South`.`South$` `South$`" & Chr(13) & "" & Chr(10) & "WHERE (`South$`.`BDE Territory`='BDE0804')" _ ) .Name = "Query from Excel Files_1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub |
All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com