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
|