vba 如何在Excel中无法以图形方式显示的外部数据查询中添加参数？

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

提问by Rory

I often use MS Excel's Get External Datato 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.

我经常使用 MS ExcelGet External Data来创建简单的报告 - 对数据库运行查询并在 Excel 中很好地显示。Excel 的强大功能，如过滤和数据透视表以及用户熟悉的界面，使其非常适合这一点。但是，Microsoft Query 的一个限制是您无法向无法以图形方式显示的查询添加参数，这极大地限制了您可以编写的 SQL。

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

“无法图形化显示的查询中不允许使用参数”的错误有什么解决办法吗？

采纳答案by Rory

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.

如果您有 Excel 2007，您可以编写 VBA 来更改工作簿中的连接（即外部数据查询）并更新 CommandText 属性。如果您只是?在需要参数的位置添加，那么下次刷新数据时，它会提示输入连接值！魔法。当您查看 Connection 的属性时，Parameters 按钮现在将处于活动状态并且可以正常使用。

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.

例如，我会编写一个宏，在调试器中逐步执行它，并使其适当地设置 CommandText。完成此操作后，您可以删除宏 - 它只是更新查询的一种方式。

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

回答by behonji

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:

Excel 的 SQL Server 查询界面不会让您拥有自定义参数。解决此问题的一种方法是创建一个通用 Microsoft Query，然后添加参数，然后将参数化查询粘贴到连接的属性中。以下是 Excel 2010 的详细步骤：

1. Open Excel
2. Goto Datatab
3. From the From Other Sourcesbutton choose From Microsoft Query
4. The "Choose Data Source" window will appear.  Choose a datasourceand 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 Definitiontab
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 OKto get back to the "Import Data" window
2. Choose PivotTable Report
3. ClickOK
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 Datatab and click the connections Propertiesbutton
1. Click the Definitiontab
2. In the "Command text:" section, Paste in the real SQL Querythat you want with the same number of parameters that you defined earlier.
3. Click the Parameters...button 
1. enter the Prompt valuesfor each parameter
2. Click OK
4. Click OKto close the properties window
10. Congratulations, you now have parameters.

1. 打开 Excel
2. 转到数据选项卡
3. 从来自其他来源按钮选择来自 Microsoft Query
4. 将出现“选择数据源”窗口。  选择一个数据源并单击OK。
5. 查询Qizard
1. 选择列：将出现窗口。目标是创建一个通用查询。我建议从一张小表格中选择一列。
2. 过滤数据：点击下一步
3. 排序顺序：只需单击下一步
4. 完成：只需单击“完成”。
6. 将出现“导入数据”窗口：
1. 单击属性...按钮。
1. 选择定义选项卡
2. 在“命令文本：”部分添加 包含 Excel 参数的 WHERE 子句。现在添加您想要的所有参数很重要。例如，如果我想要两个参数，我可以添加：
   WHERE 1 = ? 和 2 = ?
3. 单击“确定”返回“导入数据”窗口
2. 选择数据透视表
3. 单击确定
7. 系统将提示您输入每个参数的参数值。
8. 输入参数后，您将进入数据透视表
9. 转到“数据”选项卡并单击“连接属性”按钮
1. 单击定义选项卡
2. 在“命令文本：”部分中，使用您之前定义的相同数量的参数粘贴您想要的实际 SQL 查询。
3. 单击参数...按钮 
1. 输入每个参数的提示值
2. 单击确定
4. 单击确定关闭属性窗口
10. 恭喜，您现在有了参数。

回答by Aaron DeMille

Easy Workaround (no VBA required)

简单的解决方法（不需要 VBA）

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"

1. 右键单击表，展开“表”上下文菜单，选择“外部数据属性”
2. 单击“连接属性”按钮（仅在工具提示中标记）
3. 转到选项卡“定义”

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.

从这里开始，通过添加 '?' 直接编辑 SQL 任何你想要参数的地方。工作方式和以前一样，只是你不会被唠叨。

回答by gabrys

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

是 - 解决方案是将工作簿保存到 XML 文件（例如“XML Spreadsheet 2003”）并在记事本中将此文件编辑为文本！使用记事本的“搜索”功能查找查询文本并将您的数据更改为“？”。

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

在excel中保存并打开，尝试刷新数据，excel将监控参数。