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