使用 VBA 在 Excel 2010 中查询表 (QueryTables) 使用 VBA 创建许多连接

Question

我正在跟踪在另一个网站上找到的代码。这是我的代码的基础知识：

Dim SQL As String
Dim connString As String

connString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase"
SQL = "Select * from SomeTable"

With Worksheets("Received").QueryTables.Add(Connection:=connString, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL)
.Refresh

End With

End Sub

这样做的问题是，每次他们点击分配给它的按钮时，它都会创建一个新连接，并且似乎永远不会丢失它。我在测试后打开电子表格，在“连接”下列出了许多版本的连接。 联系 连接1 Connection2

我似乎也找不到关闭或删除连接的方法。如果我在“.Refresh”之后添加“.delete”，则会收到 1004 错误。由于后台正在刷新数据，无法执行此操作。

有什么想法如何关闭或删除连接吗？

Answer 1

您可能会问自己为什么每次都在代码中创建 QueryTable。这样做是有原因的，但通常没有必要。

查询表更典型的是设计时对象。也就是说，您创建一次 QueryTable（通过代码或 UI），然后刷新 QueryTable 以获取更新的数据。

如果您需要更改底层 SQL 语句，您有一些选择。您可以设置提示输入值或从单元格获取值的参数。更改 SQL 的另一个选项是在现有 QueryTable 的代码中更改它。

Sheet1.QueryTables(1).CommandText = "Select * FROM ...."
Sheet1.QueryTables(1).Refresh

您可以通过更改 CommandText 选择不同的列甚至不同的表。如果是不同的数据库，您将需要一个新的连接，但这种情况很少见。

我知道这并不能直接回答您的问题，但我认为确定您是否真的需要每次都添加 QueryTable 是第一步。

有关参数的更多信息，请参阅 http:// /dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/ 这是 2003 年的版本，因此与后续版本很少有不一致的地方。基础知识是相同的，只是如果您使用的是 2007 或更高版本，您可能需要了解 ListObject 对象。

Answer 2

我有同样的问题。前面的答案是 PITA，这是朝着正确方向迈出的明确一步。

然而，它确实允许我改进我的搜索，获胜者是...

http ://msdn.microsoft.com/en-us/library/bb213491(v=office.12).aspx

即对于您现有的 QueryTable 对象只需执行以下操作：

.MaintainConnection = False

工作得如此出色。数据刷新后不再有Access DB锁定文件。

Answer 3

您应该将连接声明为单独的对象，然后在数据库查询完成后可以将其关闭。

我面前没有 VBA IDE，所以如果有任何不准确之处，请原谅，但它应该为您指明正确的方向。

例如

Dim SQL As String
Dim con As connection

Set con = New connection
con.ConnectionString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase"

Worksheets("Received").QueryTables.Add(Connection:=con, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL).Refresh

con.close
set con = nothing

Answer 4

我发现默认情况下以这种方式创建的新连接称为“连接”。我使用的是这段代码来删除连接但保留列表对象。

Application.DisplayAlerts = False
ActiveWorkbook.Connections("Connection").Delete
Application.DisplayAlerts = True

可以轻松修改它以删除最新添加的连接（或者如果您通过索引跟踪连接）。

Application.DisplayAlerts = False
ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete
Application.DisplayAlerts = True

Answer 5

您无需使用 add 方法添加另一个查询表，只需更新连接的 CommandText 属性即可。但是您必须注意，更新 ODBC 连接的 CommandText 属性时存在错误。如果您临时切换到 OLEDB 连接，更新 CommandText 属性，然后切换回 ODBC，则不会创建新连接。不要问我为什么......这对我有用。

创建一个新模块并插入以下代码：

Option Explicit

Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "")

With WorkbookConnectionObject
    If .Type = xlConnectionTypeODBC Then
        If CommandText = "" Then CommandText = .ODBCConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection
        .ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare)
    ElseIf .Type = xlConnectionTypeOLEDB Then
        If CommandText = "" Then CommandText = .OLEDBConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection
    Else
        MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error"
        Exit Sub
    End If
    If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then
        .OLEDBConnection.CommandText = CommandText
    End If
    If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then
        .OLEDBConnection.Connection = ConnectionString
    End If
    .Refresh
End With

End Sub

此 UpdateWorkbookConnection 子例程仅适用于更新 OLEDB 或 ODBC 连接。连接不一定必须链接到数据透视表。它还修复了另一个问题，并允许您更新连接，即使存在基于同一连接的多个数据透视表。

要启动更新，只需使用连接对象和命令文本参数调用该函数，如下所示：

UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure"

您也可以选择更新连接字符串。

Answer 6

如果您想在刷新后立即删除，您应该不在后台进行刷新（使用第一个参数 -> Refresh False），以便您有正确的操作顺序

Answer 7

尝试将 QueryTable.MaintainConnection 属性设置为 False...

“如果要在刷新后保持与指定数据源的连接直至工作簿关闭，请将MaintainConnection 设置为 True。默认值为 True！但没有似乎是一个 UI 复选框（读/写布尔值）”

Answer 8

多年后仍然相关......与同样的问题作斗争，这是最有帮助的线程。我的情况是上述情况的变体，当我找到它时我会添加我的解决方案。

我使用 Access 数据库作为数据源，并在新工作表上建立查询表。然后，我再添加两个新工作表，并尝试在每个工作表上使用相同的连接建立一个查询表，但连接到不同的 Access 表。第一个查询表工作得很好，我使用 .QueryTables(1).Delete 并将查询表对象设置为 Nothing 以使其断开连接。

但是，下一张表无法使用未关闭的相同连接建立新的查询表。我怀疑（并将在下面添加解决方案）我需要在删除查询表之前删除连接。 Rasmus 上面的代码看起来像是可能的解决方案。