Changing string input in a query within a macro

M

msdrolf

I have created a query with the macro recorder in Excel 2003. During the
creation process a string input is required. I would now like to pull the
string input from a cell reference from another sheet in the workbook. Here
is the key part of the query/macro:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel
Files;DBQ=C:\Data\Macro\MacroData.xls;DefaultDir=C:\Data\Macro;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("G15"))
.CommandText = Array( _
"SELECT REGION.REGION, REGION.Name, REGION.CUSTNO, REGION.SALES" &
Chr(13) & "" & Chr(10) & "FROM `C:\Data\Macro\MacroData`.REGION REGION" &
Chr(13) & "" & Chr(10) & "WHERE (REGION.Name='DANIEL')" & Chr(13) & "" &
Chr(10) & "ORDER BY REGION.CUSTNO" _
)

In the WHERE command I want to change the string DANIEL to the input from
cell C3 in the INPUT sheet. I need to to do this about 200 times and just
want to change the name and have the macro do the rest. Is this possible.
Thanks.
 
T

Tom Ogilvy

"WHERE (REGION.Name='DANIEL')"

becomes

"WHERE (REGION.Name='" & worksheets("Input").Range("C3").Value & "')"
 
M

msdrolf

Many thanks - your suggestion works beautifully.

Tom Ogilvy said:
"WHERE (REGION.Name='DANIEL')"

becomes

"WHERE (REGION.Name='" & worksheets("Input").Range("C3").Value & "')"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top