Pass a filename to a query

[Mike Fogleman]

I am getting a filename from the user with this code, but can't seem to get
it passed to the .CommandText part of the query. Can you point me in the
right direction?


Sub Get_Leak_Data()
Dim filename

Sheets.Add
ActiveSheet.Name = "Database"
Range("A1").Select
fileToOpen = Application _
.GetOpenFilename("Database Files (*.dbf), *.dbf")
filename = Mid(fileToOpen, 9, 7)

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;CollatingSequence=ASCII;DBQ=c:\LES5;DefaultDir=c:\LES5;Deleted=0;Drive
r={Microsoft dBase Driver (*.dbf)};DriverId=533;FIL=dBase" _
), Array( _
"
5.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Statis
tics=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT filename.TESTREAD, filename.TESTFREQ, filename.TESTDATE,
filename.REPFREQ, filename.REPDATE" & Chr(13) & "" & Chr(10) & "FROM
filename filename" & Chr(13) & "" & Chr(10) & "ORDER BY filename.TESTREAD
DESC" _
)
.Name = "Query from CLI Laf"
.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

 

[Bill Manville]

.CommandText = Array( _
"SELECT filename.TESTREAD, filename.TESTFREQ, filename.TESTDATE,
filename.REPFREQ, filename.REPDATE" & Chr(13) & "" & Chr(10) & "FROM
filename filename" & Chr(13) & "" & Chr(10) & "ORDER BY filename.TESTREAD
DESC" _
)

filename is a VBA variable and is not known to the database driver that
will be interpreting the SQL SELECT statement.

So you need to get the value of it into the SELECT statement.
Something like this:

.CommandText = Array( _
"SELECT " & filename & ".TESTREAD, " & filename & ".TESTFREQ, " &
filename & ".TESTDATE," & filename & ".REPFREQ," & filename & ".REPDATE" &
Chr(13) & "" & Chr(10) & "FROM " & filename & " " & filename & Chr(13) & ""
& Chr(10) & "ORDER BY " & filename & ".TESTREAD DESC" _
)

You might need "SELECT '" & filename & "'.TESTREAD, '" & filename &
"'.TestFREQ, '" etc.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup