Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing criteria via Get External Data from a macro
I've been trying to figure out how to pass criteria to MS Query when
refreshing data with a macro. I'm planning on having the criteria to be passed hard coded into the macro, along the lines of: Refresh All with STATE = 'WI' (or sometimes Refresh All with STATE = "WA" or "OR") When the refresh is done, 'save as' the workbook without the embedded query and without the macro in the spreadsheet as 'filename-passed-criteria' and then do another refresh with another set of criteria, save it as a seperate file, and so on. Also, there's more than one Get External Data instance in the spreadsheet that will have to have criteria passed to it. I hope someone can help point me in the right direction! thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing criteria via Get External Data from a macro
Look at the SQL portion of the query
Select * from Table where State = 'WT' in you code change this to vVar = "WT" sSql = "Select * from Table where State ='" & vVar & "'" You can also add a criteria in msquery and rather than put in a value in the criteria put in square backets and you will bet a prompt to enter the value when you refresh. If you view the SQL in msquery, it will look like where State = ? Also, back in Excel you can right click on the query (once it is a parameter query) and select parameter from the pop up and in that, designate a cell to retrieve the value from. -- Regards, Tom Ogilvy "Matthew" wrote in message om... I've been trying to figure out how to pass criteria to MS Query when refreshing data with a macro. I'm planning on having the criteria to be passed hard coded into the macro, along the lines of: Refresh All with STATE = 'WI' (or sometimes Refresh All with STATE = "WA" or "OR") When the refresh is done, 'save as' the workbook without the embedded query and without the macro in the spreadsheet as 'filename-passed-criteria' and then do another refresh with another set of criteria, save it as a seperate file, and so on. Also, there's more than one Get External Data instance in the spreadsheet that will have to have criteria passed to it. I hope someone can help point me in the right direction! thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing criteria via Get External Data from a macro
Thanks for the help - it's given me something to work with. I'm still
having trouble understanding the use of setting the variables in your example (vVar and sSql) to using them in the macro to refresh the data. I've tried some variations but without success. thanks for any additional help. Matthew "Tom Ogilvy" wrote in message ... Look at the SQL portion of the query Select * from Table where State = 'WT' in you code change this to vVar = "WT" sSql = "Select * from Table where State ='" & vVar & "'" You can also add a criteria in msquery and rather than put in a value in the criteria put in square backets and you will bet a prompt to enter the value when you refresh. If you view the SQL in msquery, it will look like where State = ? Also, back in Excel you can right click on the query (once it is a parameter query) and select parameter from the pop up and in that, designate a cell to retrieve the value from. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting External Data based on criteria insde of the external data | Excel Discussion (Misc queries) | |||
Passing Dates criteria in MS Query | Excel Discussion (Misc queries) | |||
Parameter/Criteria option not available for External Data Query | Excel Discussion (Misc queries) | |||
Help with passing date value into Auto filter criteria | Excel Programming | |||
passing spreadsheet cell data to macro | Excel Programming |