Query parameters in an external connection to sql server

Question

I want to do exactly this: http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/ but I want to do it in Excel 2010 and also I am querying SQL Server instead of MS Access. Is it possible?

To elaborate on what I want from that link exactly is to have query parameters that reference cells in the worksheet. At present I'm using VBA and a cell change trigger event. Is there a simpler way?

---

NOTE: Anyone who is using the method in the accepted answer, if you want your spreadsheet to work on a computer that does not have SQL Server installed on it, then you might find this helpful: https://dba.stackexchange.com/a/109221/46815

Solution

So I finally figured it out. Here are my steps.

1. Open a new Excel worksheet (I'm using Excel 2010)
2. Click on the Data tab on the ribbon
3. Click the dop down on "From other sources"
4. Select "From Microsoft Query"
5. Select your datasource
6. Pick any table to populate the Columns in your query box - this doesn't matter as we'll override it later - and click Next
7. Keep clicking next until you get to the finish button, then click finish
8. Click "Properties..."
9. Click the "Definition" tab
10. The connection type should say "Database Query" - if it says that, you can add parameters, if not, it doesn't seem to be able to add parameters
11. Update your Command Text with the query you want to use to access the SQL database - haven't tried with other databases, but I'm sure it will work on others
12. In order to create a parameter, simple put a ? in the where clause. e.g. select * from table1 where col1 = ?

There are some issues with adding parameters. They only work in the final where clause. i.e. if you are using subqueries, pivots, cte's etc you can't add parameters in those. Only at the end in the final where clause

13. Click "Ok" twice - a pop up will appear asking you for the parameter

14. Fill anything in - we'll change this later

15. Click anywhere in the results and click "Properties" on the Data tab on the ribbon

16. Click on the little hand property icon

17. Click on the Definition Tab

18. You will now see the "Parameters..." button is clickable. Click it and change the option from prompt for value to Get value from the following cell

If anyone finds a quicker, simpler way - I'm all ears, but this works consistently and with really big complicated queries. So for us, it is the right solution

Comments

[Admin]

What is wrong with your approach/why don't you like it? You can do the same, as this is possible using Microsoft Query - the database *shouldn't matter! Did you try it yet and did it not work? Are you stuck at a certain point?

[Admin]

@DaveRook Yes I've tried it, for OLE DB drivers it looks like there just isn't an option for parameters. The button is greyed out. My approach is fine but what a mission to write a bunch of macros when Excel can probably do it for me. Plus macros are less robust, if I move the cell then I have to go and manually change all the references in the code.

[Admin]

I am sitting with the same problem - does anyone have a solution?

[Admin]

I managed to do it with a simple query - you have to use the "From Mocrosoft Query" option in "From other sources", but as soon as I tried a more complicated query (using a pivot) it game me other errors. I am going to try invoking a Stored Proc which others have managed with.

[Admin]

@PeterMunnings Would you mind writing up the full procedure you used to get it working with a simple query as an answer please?

[Dan]

Finally got a chance to do this, sorry it took so long! This works!!! My only suggestion would be to expand on point 3 as in my case I didn't have the connection set up and I'm not sure for example between SQL Server or SQL Server native client 11.0 etc. Otherwise great answer!