Run an SQL Query With a Parameter from Excel 2007

Solution 1:

Dunno why MS has made this so complicated, You will have to use Microsoft Query.

Click on Data -> From External Sources -> From Microsoft Query. CHoose Data source comes up. Select SQL Server, enter the Auth details, and select the table

Click on Next, don't select any filtering criteria, choose sort by criteria, click on next. Now, click on View/Edit in MS Query instead of selecting Return to Excel

Click on Finish. Now in MS Query, Click on Criteria -> Add Criteria, choose the operator and let the value be []

Click on File -> return data to Excel. Now Excel should prompt you for the parameter, select the relevant cell

To edit the parameters, click on Data -> Properties -> Finger icon -> Definition -> parameters

You can also use the SQL query editor and type in the query with the joins and put a ? against the field where the parameter has to be fetched.

Solution 2:

It's never too late for a shameless plug...

There's this great little plugin for Excel...(disclaimer: I'm the author)

It's an Excel plugin that adds a database engine and an SQL IDE to Excel.

It lets you do SQL operations on anything that is marked as a table inside Excel. You can query across workbooks, and also use tables from an external database (SQL Server/Sqlite/Postgres/Access/MySql) in the same query.

It has SQL syntax highlighting, statement completion and a bunch of other cool stuff. It uses SQLite under the hood, but can use external db engines to process the data as well.

I do charge for it tho, but there's a free trial which you can use for 30 days. It's called ThingieQuery.

Sorry about the plug, hopefully it's not too inappropriate.