Creating and Editing Parameterized Queries in Excel 2007

I spotted this question on Super User about creating parameterized queries in Excel 2007 and attempted to answer it – as I thought it would be pretty easy.

And well – it was easy – the part part where you create the query, that is. Now creating parameterized queries – now that’s something totally different. I searched around a bit for documentation on doing so – and found that any articles on the same were woefully inadequate. To be specific: There are plenty of articles on how to change the parameters – just head over to Properties -> Query Definition -> Parameters. Ok, cool. Now hang on, the Parameters button is disabled. How the heck am I supposed to get it enabled ? I had a ball of a time trying to figure out a way to do it – and in the end, found the answer, and posted it. Figured might as well post it here for my reference… and for others who are searching for a way to do the same.

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 Authentication 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.

  1. Man this has had me going in circles for an hour, what a pain, a lot more difficult than previous version of Excel…ick, MS Query is kinda lame as it is.

    Big help so mucho thanks.

  2. when we change the file address, but msquary put the data on same address, if file not found then is show the error can it possible that quary put the open file address only not on particular address

  3. It looks like it works only with simple cases, where you can use the MS Query Wizard.

    I’m trying to count the entries from 3rd-party SQL Server database table grouped by persons and filtered by date. The query itself is simple one, but the date field is text in format ‘’ with empty values partly as Null and partly as ”. So the filter condition I get to work at last is:

    WHERE (CASE WHEN “_ImplementationStatus”.RegistrationDate = ” OR “_ImplementationStatus”.RegistrationDate Is Null THEN Null ELSE CAST(Substring(“_ImplementationStatus”.RegistrationDate, 7, 4) + ‘-‘ + Substring(“_ImplementationStatus”.RegistrationDate, 4, 2) + ‘-‘ + Substring(“_ImplementationStatus”.RegistrationDate, 1, 2) AS datetime) END) > GetDate()

    I need to replace GetDate() with a date from Excel cell.

    Now with complex queries in MS Query wizard you can open only SQL view. And you arent allowed to add parameters there.
    The only place you can add a parameter for complex queries is in Command text on Definition page of Connection Properties. You add a ? instead of value/function, and when you press OK, you are asked for parameter value or allowed to specify the cell the parameter value is stored. It has worked for me so long (I had parameters which simply referred to some field in MS SQL database table), but not with current query. Now I get the error message “Incorrect syntax near the keyword ‘from'”. It looks like MS Query is forwarding the query text to MS SQL Server as it is, without recognizing ? as a parameter.

  4. Thanks for your notes, I agree there are lots of inadequate articles, but I am still at a loss as to how to enter a simple Date Range and link to Excel Worksheet Cells. I’ve spent hours with lots of these articles but it remains a mystery.

  5. Excellent. this is what i was looking for. spent the whole day to find how to add parameters and how to open the query analyzer in excel.

    thanks a ton

Leave a Reply

%d bloggers like this: