Lookup from Excel to Access

[pat.morley]

Is there a way to use MS Query to lookup a range of values from a
spreadsheet? Currently we use an excel front end to let users query
an access db - ms query allows them to enter a value into the
parameter window. I'd like to be able to pass a list of values -
rather than doing this one at a time.

The whole process for the end users has been simplified to pushing a
button - fyi.


Thanks much.

 

[ward376]

Yes, you can tell MS Query that a cell value is a parameter. You can
also tell it to always use that range as the parameter for a query abd/
or to refresh the query if the value changes.

Cliff Edwards

 

[ward376]

Here's a link that may help you:

http://www.codeforexcelandoutlook.com/DQYFiles.html

Rather than saving a query file as featured in the link, I usually set
up a very hidden sheet with the query range stored on it and refresh
the query when the procedure is run. Just one short line of code to
trip it then: srcSh.QueryTables(i).Refresh BackgroundQuery:=False.

I'm not saying one technique is better than the other, just offering
options.

Cliff Edwards