Using a Cell Reference in a SQL Query

I can't believe this is so hard.  I think I've seen 100 answers on EE to this exact question and I still can't figure it out.

I have a SQL Server database with a table called Branches.  I want to type a branch number into cell B7 of my spreadsheet and have Excel go out to SQL Server and get all of the information for the specified branch.

In Excel I went to Data > Import External Data > New Databse Query
I found the table.
I was able to create the following SQl... select * from "Autopart"."dbo"."Branches" where branch = '2024'

The problem is that I had to hard code the value 2024 when I really want it to look at cell B7 and use whatever value it finds.

I know nothing about VBA or ADO.

From everything I've read it seems quite doable, I just can't figure it out.

Does anybody have any "dummy" level instructions that might help me?

...timmy
CiciOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
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.

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CiciOAuthor Commented:
Brilliant.  I shall name my next born child "Kevin".

I knew there had to be an easy way.

Thank you Thank you Thank you.

...timmy
zorvek (Kevin Jones)ConsultantCommented:
You're welcome :-)

Kevin
HyperDawnCommented:
This has helped me a lot at work where we are using dual database and spreadsheet systems.

You would have thought this would be a standard feature in Excel!!

Thank you very much :-]
cpvCommented:
There is a problem with this approach, when you close your spreadsheet (and save) then reopen, the cells are not saved in the parameter dialog box, and you have to reenter them all over again.... has anybody found a solution to this?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.