SQL query in Excel referencing an existing Excel data cell

[rfjaster]

I am trying to create a SQL query in Excel for a customer to run and generate a report. I have the query written but would like the where clause in query to reference a cell on the spreadsheet. Basically, the customer could update the cell and the script could be updated automatically to bring in the data from the SQL database.

I haven't had any luck editing the query syntax in the command text section of the Excel connection. Any ideas?

The query would be something similar to:

Select * from table where field = A1 -- A1 being the Excel cell where the users would edit before updating the spreadsheet.

Thanks

 

[jbenson001]

This is not a SQL Server issue. You should be able to search on line to find an answer.

 

[jbenson001]

http://www.sqlmag.com/Article/ArticleID/9127/sql_server_9127.html

 

[SkipVought]

post in Forum707.

Is the field defined as TEXT or NUMERIC?

If numeric...

dim sSQL as string

sSQL = "Select * from table where field = " & YourSheetObject.[A1]

If test...

dim sSQL as string

sSQL = "Select * from table where field = '" & YourSheetObject.[A1] & "'"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[TysonLPrice]

From Experts Exchange:

To create a database query that prompts for filter constants each time the query is updated or that uses one or more cell values as query parameters, create the database query by selecting the menu command Data->Import External Data->New Database Query. Follow the prompts to the point in the Query Wizard where the Filter Data dialog is displayed. Add the desired filter criteria using constant values - the parameter substitution part of the query will be implemented in a later step but there must be at least one filter criterion for each desired parameter. Click Next to view the Sort dialog and Next again to view the Finish dialog. Click the radio button "View data or edit query in Microsoft Query" and click Finish.

If working with an existing query then right click on the query data and select "Edit Query". Click Next until the Finish dialog is displayed. Click the radio button "View data or edit query in Microsoft Query" and click Finish.

The Microsoft Query window is now displayed. In the middle of the dialog each of the defined filter criteria are listed, one per column. In each criteria where a parameter is desired change the value from the constant value previously entered to a question enclosed in square brackets such as "[Enter filter value:]". Close the Microsoft Query window. When the window closes an update will be initiated and each parameter will be requested. Enter each parameter requested to complete the query. Any time the query is refreshed the query will again ask for each parameter.

To use cell values to drive the query instead of entering them each time, right click on the query data and select Parameters. The "questions" entered using the Microsoft Query window are listed in the left column. To convert any one to use a cell value instead of prompting for the value, select the "question" and click the radio button "Get the value from the following cell:" Enter the cell address in the text entry box below the radio button. To update the query every time the cell value changes, check the check box "Refresh automatically when cell value changes". Repeat for each "question" to be converted. Click OK.