putting a variable in an SQL statement

[mcgerks]

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

 

[Al Shaw]

"FROM `C:WINNTProfilestc4387DESKTOPtest & FileName` & .table1 table1")


You were almost there, just needed to break down the text into "quote" & variable & "quote" - the whole of the line above was being passed through into the query as it was written, rather than picking up the variable.


"FROM `C:WINNTProfilestc4387DESKTOPtest" & FileName & "` & .table1 table1")
This message was edited by Al Shaw on 2002-10-28 12:19

 

[mcgerks]

Thank you, that was helpful, but now I have another problem.

"SELECT table1.`book 1 Cell A1`, table1.`book 1 Cell B1`" & Chr(13) & "" & Chr(10) & "FROM `C:WINNTprofilestc4387desktoptestBook1`.table1 table1" _

I named a table in an excel spread sheet table1, containing cells A1 and B1. In cell A1, I wrote: book 1 Cell A1. In cell B1, I wrote: book 1 Cell B1. How can I replace in the SELECT statement the actual string in the cell: `book 1 Cell B1` with cell name. For example (I tried this but it does not work, just suedo code):

"SELECT table1.range("a1").value,