My World A Blog On All The Things That Happen In My Life

Went to this nice little Portuguese Cafe near my apartment. Place was pretty nice.DSC03076DSC03075DSC03074DSC03071DSC03070DSC03067DSC03069
Status » Grid 2 gameplay video - Faceoff run with the Nissan Fairlady Z: http://t.co/IiAPdIA9sS via @YouTube

Creating and Editing Parameterized Queries in Excel 2007
about Computing on 12/10/2010 - like it?

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.

Tags: , , , , ,


11 comments - really? way cool!

Blogged: Creating and Editing Parameterized Queries in Excel 2007: I spotted this question… http://goo.gl/fb/GFNPF

Written by Sathya on 11/10/2010

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.

Written by Adam on 01/06/2011

Thank you.
I’ve been lookign for that piece of the puzzle all day.
Thank you.

Written by AB on 11/08/2011

Thanx so much for this.

Written by Rooperi on 14/09/2011

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

Written by Mukesh Kumar on 29/09/2011

But what if we don’t have SQL Servers? Only Oracle?

Written by Inbal on 24/10/2012

@inbal the DB server is irrelevant; if it’s an Oracle server, the connection string will be different.

Written by sathya on 24/10/2012

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 ‘dd.mm.yyyy’ 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.

Written by Arvi Laanemets on 31/01/2013

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.

Written by keith on 20/03/2013

Thank you!

Written by xkz on 01/04/2013

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

Written by Jimit on 27/02/2014

Leave some comment love