vba - How to add parameters to an external data query in Excel which can't be displayed graphically?

Question

I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that can't be displayed graphically, which considerably limits the SQL you can write.

Is there any solution to the error "parameters are not allowed in queries that can't be displayed graphically"?

Answer 1

Excel's interface for SQL Server queries will not let you have a custom parameters.  A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection's properties.  Here are the detailed steps for Excel 2010:

1. Open Excel
2. Goto Data tab
3. From the From Other Sources button choose From Microsoft Query
4. The "Choose Data Source" window will appear.  Choose a datasource and click OK.
5. The Query Qizard
6. Choose Column: window will appear.  The goal is to create a generic query. I recommend choosing one column from a small table.
7. Filter Data: Just click Next
8. Sort Order: Just click Next
9. Finish: Just click Finish.

The "Import Data" window will appear:

1. Click the Properties... button.
2. Choose the Definition tab
3. In the "Command text:" section add a WHERE clause that includes Excel parameters.  It's important to add all the parameters that you want now.  For example, if I want two parameters I could add this:
   WHERE 1 = ? and 2 = ?
4. Click OK to get back to the "Import Data" window

Choose PivotTable Report Click OK You will be prompted to enter the parameters value for each parameter. Once you have enter the parameters you will be at your pivot table Go batck to the Data tab and click the connections Properties button

1. Click the Definition tab
2. In the "Command text:" section, Paste in the real SQL Query that you want with the same number of parameters that you defined earlier.
3. Click the Parameters... button 
4. enter the Prompt values for each parameter
5. Click OK

Click OK to close the properties window Congratulations, you now have parameters.