Pass cell data to Query

[John Gelavis]

Anyone know how to pass the contents of a cell to an sql query? I have a
worksheet that I have connected to an SQL database that I want to query to
get data. The worksheet is like an invoice. What I want is the user to input
a product code and currency then the query to return the price. It sounds
simple enough to do with a nested function but the price list is made
complex by prices in different currencies so the level of nesting allowed is
insufficient. I thought a database query would be a more appropriate
solution but I can't find any information on how to do it in this way.

 

[Ron Coderre]

I'll assume that you're already familiar with using MS Query and concentrate
on how to run a Parameter Query (Don't hesitate to post MS Query questions,
though)

In MS Query, build the query per normal.
View the Criteria window
Select the table field(s) that you want to supply cell parameter values for

For the criteria:
Enclose the parameter name in square brackets
Example: [prmCustNum]

When you run the query, you will be prompted to supply each parameter.

After the query runs properly, click the Return Data to Excel button.
On the popup window where you select the destination for the query data,
there is a [Parameters] button at the bottom.

Click the [Parameters] button
All parameters will be displayed
Select a parameter
Select "Get the value from the following cell"
Select the cell containing the parameter value
Repeat for each parameter

After completing the process, whenever you refresh the query, the values
from the parameter cells will be supplied to the SQL.

Thereafter, if you need to change the location of the parameter cells:
Right-click on the data table
Select: Parameters
That will open the Parameters window, where you will effect the changes.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP