So I finally figured it out. Here are my steps.
- Open a new Excel worksheet (I'm using Excel 2010)
- Click on the Data tab on the ribbon
- Click the dop down on "From other sources"
- Select "From Microsoft Query"
- Select your datasource
- Pick any table to populate the Columns in your query box - this
doesn't matter as we'll override it later - and click Next
- Keep clicking next until you get to the finish button, then click
finish
- Click "Properties..."
- Click the "Definition" tab
- 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
- 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
- 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
Click "Ok" twice - a pop up will appear asking you for the
parameter
Fill anything in - we'll change this later
Click anywhere in the results and click "Properties" on the Data tab on the ribbon
Click on the little hand property icon
Click on the Definition Tab
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