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

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

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