这篇文章已经足够老了,这个答案可能对OP没什么用处,但我花了很长时间试图回答同样的问题,所以我想我会用我的发现来更新它。
此答案假设您的 Excel 文档中已经有一个有效的 SQL 查询。网上有很多教程向您展示如何完成此操作,并且有很多教程解释如何向其中添加参数化查询,但似乎没有一个适用于现有的、OLE DB query.
因此,如果您像我一样,收到了一份包含有效查询的旧版 Excel 文档,但用户希望能够根据其中一个数据库字段筛选结果,并且如果您像我一样,既不是 Excel 也不是作为 SQL 专家,这也许可以帮助您。
大多数网络对此问题的回答似乎都说你应该添加一个“?”在查询中让 Excel 提示您输入自定义参数,或者将提示或单元格引用放在应该包含参数的 [括号] 中。这可能适用于 ODBC 查询,但似乎不适用于 OLE DB,在前一实例中返回“没有为一个或多个必需参数给出值”,以及“无效的列名‘xxxx’”或“未知对象”后两者中的“xxxx”。同样,使用神秘的“参数...”或“编辑查询...”按钮也不是一个选项,因为在这种情况下它们似乎永久呈灰色。 (作为参考,我使用的是 Excel 2010,但使用的是 Excel 97-2003 工作簿 (*.xls))
然而,我们可以做的是添加一个参数单元格和一个带有简单例程的按钮,以编程方式更新我们的查询文本。
首先,在外部数据表上方(或任何位置)添加一行,您可以在空单元格和按钮旁边放置参数提示(开发人员->插入->按钮(表单控件) - 您可能需要启用“开发人员”选项卡,但您可以在其他地方找到如何做到这一点),如下所示:
![[Picture of a cell of prompt (label) text, an empty cell, then a button.]](https://i.stack.imgur.com/SQyuc.png)
接下来,在“外部数据”(蓝色)区域中选择一个单元格,然后打开“数据”->“刷新全部”(下拉列表)->“连接属性...”以查看您的查询。下一节中的代码假设您的查询中已经有一个参数(连接属性->定义->命令文本),其形式为“WHERE (DB_TABLE_NAME.Field_Name = ‘默认查询参数’)”(包括括号)。显然,根据数据库表名称、数据库值字段(列)名称以及打开文档时要搜索的一些默认值,代码中的“DB_TABLE_NAME.Field_Name”和“默认查询参数”需要不同(如果您已设置自动刷新)。记下“DB_TABLE_NAME.Field_Name”值,因为您将在下一部分中需要它,以及查询的“连接名称”(可以在对话框顶部找到)。
关闭“连接属性”,然后按 Alt+F11 打开 VBA 编辑器。如果您尚未使用,请右键单击“项目”窗口中包含按钮的工作表名称,然后选择“查看代码”。将以下代码粘贴到代码窗口中(建议复制,因为单/双引号很危险且必要)。
Sub RefreshQuery()
Dim queryPreText As String
Dim queryPostText As String
Dim valueToFilter As String
Dim paramPosition As Integer
valueToFilter = "DB_TABLE_NAME.Field_Name ="
With ActiveWorkbook.Connections("Connection name").OLEDBConnection
queryPreText = .CommandText
paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
queryPreText = Left(queryPreText, paramPosition)
queryPostText = .CommandText
queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
.CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
End With
ActiveWorkbook.Connections("Connection name").Refresh
End Sub
将“DB_TABLE_NAME.Field_Name”和“连接名称”(在两个位置)替换为您的值(需要包含双引号以及空格和等号)。
将“单元格引用”替换为您的参数所在的单元格(从一开始的空单元格) - 我的单元格是第一行中的第二个单元格,所以我输入“B1”(同样,双引号是必要的)。
保存并关闭 VBA 编辑器。
在适当的单元格中输入您的参数。
右键单击按钮将 RefreshQuery 子指定为宏,然后单击按钮。查询应该更新并显示正确的数据!
笔记:
仅当查询中存在联接或其他等号出现时,才需要使用整个过滤器参数名称(“DB_TABLE_NAME.Field_Name =”),否则只需等号就足够了,并且 Len() 计算将是多余的。
如果您的参数包含在也用于连接表的字段中,则需要将代码中的“paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1”行更改为“paramPosition = InStr( Right(.CommandText, Len(.CommandText) - InStrRev(.CommandText, "WHERE")), valueToFilter) + Len(valueToFilter) - 1 + InStr(.CommandText, "WHERE")" 这样它只查找 valueToFilterafter“哪里”。
这个答案是在 datapig 的“BaconBits”的帮助下创建的,我在其中找到了查询更新的基本代码。