Excel 2007 Query Parameters
Query parameters are a very useful Excel feature allowing parametrization of database queries used to import data in Excel. They are really simple to use as well. On the Definition tab of the Connection Properties dialog there is a Parameters... button at the bottom. It gets enabled as soon as there is a parameter defined in the Command text – you define it by typing in a question mark (?) instead of a value in the WHERE clause of the query, as seen in the image below.
Unfortunately there is a limitation for using this functionality which turns out pretty unintuitive in Excel 2007. It is only supported for Microsoft Query based queries. For all other types of queries available in Excel you get the following error when you add a parameter to the Command text:
No value given for one or more parameters.
Since the error doesn't even hint at the real cause of the problem it took me some time before I figured it out. The thing to remember is: if you want to use query parameters, you must select Microsoft Query as the external data source when importing the data for the first time. This can't be changed at a later time.
