Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Msg Box input string | Excel Programming | |||
Msg Box input string | Excel Programming | |||
Run Access query from Excel, input value to query being value in c | Excel Programming | |||
Macro to edit text string data input | Excel Programming | |||
autofilter using string input | Excel Programming |