How would I put a variable in this SQL statement:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:WINNTProfilestc4387DESKTOPtestBook1.xls;DefaultDir=C:WINNTProfilestc4387DESKTOPtest;Driver={Microsoft Excel Dr" _
), Array( _
"iver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3" _
), Array(";UID=admin;UserCommitSync=Yes;")), Destination:=Range("A1"))
.Sql = Array( _
"SELECT table1.`book 1 Cell A1`, table1.`book 1 Cell B1`" & Chr(13) & "" & Chr(10) & "FROM `C:WINNTProfilestc4387DESKTOPtestbook1`.table1 table1")
In the FROM statement how could I put in a variable from what a user types in a cell, for example "test1" or "test2" and it would replace the book1 from the code above.
I tried a couple of things like:
FileName = Workbooks("master.xls").Sheets("Sheet1").Range("a5").Value
"FROM `C:WINNTProfilestc4387DESKTOPtest & FileName` & .table1 table1")
but I can't seem to get it to work
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:WINNTProfilestc4387DESKTOPtestBook1.xls;DefaultDir=C:WINNTProfilestc4387DESKTOPtest;Driver={Microsoft Excel Dr" _
), Array( _
"iver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3" _
), Array(";UID=admin;UserCommitSync=Yes;")), Destination:=Range("A1"))
.Sql = Array( _
"SELECT table1.`book 1 Cell A1`, table1.`book 1 Cell B1`" & Chr(13) & "" & Chr(10) & "FROM `C:WINNTProfilestc4387DESKTOPtestbook1`.table1 table1")
In the FROM statement how could I put in a variable from what a user types in a cell, for example "test1" or "test2" and it would replace the book1 from the code above.
I tried a couple of things like:
FileName = Workbooks("master.xls").Sheets("Sheet1").Range("a5").Value
"FROM `C:WINNTProfilestc4387DESKTOPtest & FileName` & .table1 table1")
but I can't seem to get it to work