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.

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

becomes

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

--
Regards,
Tom Ogilvy

"msdrolf" <(E-Mail Removed)> wrote in message
news:8C3B609D-2E7D-44FE-9FBC-(E-Mail Removed)...
>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.

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" <(E-Mail Removed)> wrote in message
> news:8C3B609D-2E7D-44FE-9FBC-(E-Mail Removed)...
> >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.
>
>
>