How to add parameters to an external data query in

问题:

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"?

回答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
1. Choose Column: window will appear.  The goal is to create a generic query. I recommend choosing one column from a small table.
2. Filter Data: Just click Next
3. Sort Order: Just click Next
4. Finish: Just click Finish.
6. The "Import Data" window will appear:
1. Click the Properties... button.
1. Choose the Definition tab
2. 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 = ?
3. Click OK to get back to the "Import Data" window
2. Choose PivotTable Report
3. Click OK
7. You will be prompted to enter the parameters value for each parameter.
8. Once you have enter the parameters you will be at your pivot table
9. 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 
1. enter the Prompt values for each parameter
2. Click OK
4. Click OK to close the properties window
10. Congratulations, you now have parameters.

回答2:

Easy Workaround (no VBA required)

1. Right Click Table, expand "Table" context manu, select "External Data Properties"
2. Click button "Connection Properties" (labelled in tooltip only)
3. Go-to Tab "Definition"

From here, edit the SQL directly by adding '?' wherever you want a parameter. Works the same way as before except you don't get nagged.

回答3:

If you have Excel 2007 you can write VBA to alter the connections (i.e. the external data queries) in a workbook and update the CommandText property. If you simply add ? where you want a parameter, then next time you refresh the data it'll prompt for the values for the connections! magic. When you look at the properties of the Connection the Parameters button will now be active and useable as normal.

E.g. I'd write a macro, step through it in the debugger, and make it set the CommandText appropriately. Once you've done this you can remove the macro - it's just a means to update the query.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            ' If you do have multiple connections you would want to modify  
            ' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.CommandText = "select blah from someTable where blah like ?" 
        End If
    Next
End Sub

回答4:

YES - solution is to save workbook in to XML file (eg. 'XML Spreadsheet 2003') and edit this file as text in notepad! use "SEARCH" function of notepad to find query text and change your data to "?".

save and open in excel, try refresh data and excel will be monit about parameters.