putting a variable in an SQL statement

mcgerks

New Member
Joined
Mar 24, 2002
Messages
12
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Al Shaw

New Member
Joined
Oct 27, 2002
Messages
4
"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

New Member
Joined
Mar 24, 2002
Messages
12
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,
 

Forum statistics

Threads
1,144,219
Messages
5,723,076
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top