passing criteria via Get External Data from a macro

[Matthew]

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

 

[Tom Ogilvy]

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.

 

[Matthew]

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