Changing string input in a query within a macro
 

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;PageT imeout=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.

Changing string input in a query within a macro
 

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

becomes

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

--
Regards,
Tom Ogilvy

"msdrolf" wrote in message
...
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;PageT imeout=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.

Changing string input in a query within a macro
 

Many thanks - your suggestion works beautifully.

"Tom Ogilvy" wrote:

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

becomes

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

--
Regards,
Tom Ogilvy

"msdrolf" wrote in message
...
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;PageT imeout=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.