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

Question

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

对于“无法以图形方式显示的查询中不允许使用参数”错误有什么解决方案吗？

Answer 1

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

1. 打开 Excel
2. 转到数据选项卡
3. 从来自其他来源按钮选择来自 Microsoft Query
4. “选择数据源” " 将出现窗口。  选择数据源并单击确定。
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. 恭喜，您现在拥有参数了。

Answer 2

简单的解决方法（无需 VBA）

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

从此处直接编辑 SQL通过添加“？”无论你想要一个参数。工作方式与以前相同，只是您不会被打扰。

Answer 3

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

例如，我会编写一个宏，在调试器中单步执行它，并使其适当地设置 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

Answer 4

对于 Excel 2013，即使查询文本包含“?”等参数，连接对话框中的“参数”按钮仍保持禁用状态。

将参数插入查询文本，如下所示：

declare @sd datetime, @ed datetime
set @sd = '2022-01-01' 
set @ed = '2022-01-31' 

select * 
from dbo.Table1 
where date between @sd and @ed

在 VBA 中添加：

Public SQLParams As New Dictionary 'Requred Reference "Microsoft Scripting Runtime"

Sub Button1_Click()
  SQLParams("sd") = "'2022-02-01'"
  SQLParams("ed") = "'2022-02-28'"
      
  UpdateQuery SQLParams
End Sub

'Update params in all Query
Sub UpdateQuery(ByRef SQLParams As Dictionary)
  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
      odbcCn.CommandText = SetParamValues(odbcCn.CommandText, SQLParams)
      odbcCn.Refresh
    ElseIf cn.Type = xlConnectionTypeOLEDB Then
      Set oledbCn = cn.OLEDBConnection
      oledbCn.CommandText = SetParamValues(oledbCn.CommandText, SQLParams)
      oledbCn.Refresh
    End If
  Next
End Sub

Function SetParamValues(SQL As String, ByRef Params As Dictionary) As String
  Dim re As New RegExp, Matches 'Requred Reference "Microsoft VBScript Regular Expressions 5.5"
  
  Dim paramName As Variant, paramValue As String
     
  SetParamValues = SQL
  
  re.IgnoreCase = True
  re.MultiLine = True
  
  For Each paramName In Params.Keys()
    re.Pattern = "(set\s+\@" + paramName + "\s*=\s*)(\'[^\']*\')"
    
    paramValue = Params(paramName)
     
    SetParamValues = re.Replace(SetParamValues, "$1" + paramValue)
  Next 'For Each paramName In Params.Keys()
End Function

Answer 5

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

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