logo标签列表

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

excelvbaexcel-2007ms-query
40
我经常使用MS Excel的"获取外部数据"来创建简单的报表，通过对数据库运行查询并在Excel中漂亮地显示出来。Excel的强大功能，如筛选和数据透视表以及熟悉的用户界面，使其非常适合此类应用。但是，Microsoft Query的一个限制是您无法向不能以图形方式显示的查询中添加参数，这严重限制了您能编写的SQL。
"无法在不能以图形方式显示的查询中添加参数"错误是否有解决方案？
5个回答
57

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

  1. 打开Excel
  2. 进入数据选项卡
  3. 从来自其他数据源按钮选择来自 Microsoft Query
  4. 将出现“选择数据源”窗口。选择数据源，点击确定。
  5. 查询向导
    1. 选择列：会出现一个窗口。目标是创建一个通用查询。我建议从一个小表中选择一列。
    2. 筛选数据：只需点击下一步
    3. 排序顺序：只需点击下一步
    4. 完成：只需点击完成。
  6. 将出现“导入数据”窗口：
    1. 点击属性...按钮。
      1. 选择定义选项卡
      2. 在“命令文本:”部分添加包含Excel参数的WHERE子句。现在添加所有想要的参数很重要。例如，如果我想要两个参数，可以添加这个：
        WHERE 1 = ? and 2 = ?
      3. 点击确定返回“导入数据”窗口
    2. 选择数据透视表
    3. 点击确定
  7. 你将被提示为每个参数输入参数值。
  8. 一旦你输入了参数，你将进入到数据透视表。
  • 回到Data选项卡，点击连接的属性按钮
    1. 点击定义选项卡
    2. 在“命令文本：”部分粘贴你想使用的实际SQL查询，该查询需要与之前定义的参数数量相同。
    3. 点击参数...按钮
      1. 为每个参数输入提示值
      2. 点击确定
    4. 点击确定关闭属性窗口
  • 恭喜，现在你有了参数。
    • 你能否在SQL Server查询中添加参数？例如连接到SQL Server数据库？ - Dan
    1
    太好了。我一直想知道如何在其中获取那些参数。 - Jeff Davis
    工作正常，但在使用LIKE时存在一些问题。如果我在命令文本中输入一些文本，比如 imagine LIKE ?，并在单元格中输入'foobar'，那么它可以正常工作，但是使用foo%就不起作用了。 - Waza_Be
    1
    这是正确的答案，应该标记为正确。 - Geoffrey
    28

    简易解决方案（无需 VBA）

    1. 右键点击表格，展开 "Table" 上下文菜单，选择 "External Data Properties"
    2. 单击 "Connection Properties" 按钮（仅在工具提示中标记）
    3. 前往 "Definition" 选项卡

    从这里开始，通过在 SQL 中添加“?” 直接编辑参数。与以前的方式一样工作，但不会有烦人的提示。

    2
    谢谢！这是一个非常好的解决方案——快速、简单、没有烦扰，并且允许直接选择将保存参数的Excel单元格。我认为这应该是被采纳的答案。 - Assad Ebrahim
    我发现还有相当多的步骤，但我确实觉得这个答案最有帮助。从这里开始，我可以自己琢磨一下了。谢谢！ - Erick Robertson
    每当我尝试这个解决方案时，Excel就会挂起。如果我指定明确的值，它就可以正常工作。但是一旦尝试添加参数，它就会挂起并最终崩溃！ - jamiebarrow
    1
    当您运行ODC一次并且SQL命令中有一个“？”时，Excel不会要求您输入或提供参数，因此最终需要使用VBA代码。 - Fandango68
    7
    如果您使用的是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
    0
    在 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
    -1

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

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

    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接