Excel 2010 中的 Query Tables (QueryTables) with VBA with VBA 创建许多连接

Query Tables (QueryTables) in Excel 2010 with VBA with VBA creating many connections

提问by DavidStein

I'm following code I found on another site. Here's the basics of my code:

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

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

The problem with doing this is every single time they hit the button assigned to this it creates a new connection and doesn't ever seem to drop it. I open the spreadsheet after testing and there are many versions of the connection listed under Connections. Connection Connection1 Connection2

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

I can't seem to find a way to close or delete the connections either. If I add ".delete" after ".Refresh" I get a 1004 error. This operation cannot be done because the data is refreshing in the background.

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

Any ideas how to close or delete the connection?

任何想法如何关闭或删除连接？

回答by Dick Kusleika

You might ask yourself why you're creating a QueryTable every time in your code. There are reasons to do it, but it usually isn't necessary.

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

QueryTables are more typically design-time objects. That is, you create your QueryTable once (through code or the UI) and the you Refresh the QueryTable to get updated data.

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

If you need to change the underlying SQL statement, you have some options. You could set up Parameters that prompt for a value or get it from a cell. Another option for changing the SQL is changing it in code for the existing QueryTable.

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

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

You can select different columns or even different tables by changing CommandText. If it's a different database, you'll need a new connection, but that's pretty rare.

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

I know that doesn't answer your question directly, but I think determining whether you really need to add the QueryTable each time is the first step.

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

For more on Parameters, see http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/It's for 2003, so there are few inconsistencies with later versions. The basics are the same, you just may need to learn about the ListObject object if you're using 2007 or later.

更多参数见http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/是2003年的，所以和后面的版本几乎没有不一致。基本原理是相同的，如果您使用的是 2007 或更高版本，则可能只需要了解 ListObject 对象。

回答by Yotool

I had the same issue. The previous answer while a definite step in the right direction is a PITA.

我遇到过同样的问题。以前的答案虽然朝着正确方向迈出的明确一步是 PITA。

It did however allow me to refine my search and the winner is...

然而，它确实让我能够优化我的搜索，获胜者是......

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

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

i.e. for your existing QueryTable Object just do this:

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

.MaintainConnection = False

Works ever so swell. No more Access DB lock file after the data is refreshed.

作品永远如此膨胀。数据刷新后不再有Access DB锁文件。

回答by st0000

You should declare the connection as a separate object then you can close it once the database query is complete.

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

I don't have the VBA IDE in front of me, so excuse me if there are any inaccuracies, but it should point you in the right direction.

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

E.g.

例如

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

回答by Brian Pressler

Instead of adding another query table with the addmethod, you can simply update the CommandText Property of the connection. However you have to be aware that there is a bug when updating the CommandTextproperty of an ODBC connection. If you temporarily switch to an OLEDB connection, update your CommandTextproperty and then switch back to ODBC it does not create the new connection. Don't ask me why... this just works for me.

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

Create a new module and insert the following code:

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

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

This UpdateWorkbookConnectionsubroutine only works on updating OLEDB or ODBC connections. The connection does not necessarily have to be linked to a pivot table. It also fixes another problem and allows you to update the connection even if there are multiple pivot tables based on the same connection.

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

To initiate the update just call the function with the connection object and command text parameters like this:

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

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

You can optionally update the connection string as well.

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

回答by Rasmus Remmer Bielidt

I've found that by default new connections created this way are called "Connection". What I am using is this snippet of code to remove the connection but retain the listobject.

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

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

It can easily be modified to remove the latest added connection (or if you keep track of the connections by their index).

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

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

回答by Kodak

If you want to delete if right after refresh you should do the refresh not in the background (using first parameter -> Refresh False) so that you have proper sequence of actions

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

回答by Leighton

Try setting the QueryTable.MaintainConnection property to False...

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

"Set MaintainConnection to True if the connection to the specified data source is to be maintained after the refresh and until the workbook is closed. The default value is True! And there doesn't seem to be a UI check box for this (Read/write Boolean)"

“如果在刷新后和工作簿关闭之前要保持与指定数据源的连接，请将MaintainConnection 设置为True。默认值为True！并且似乎没有用于此的UI 复选框（阅读/写布尔值）”

回答by Jim Snyder

Still relevant years later...battling the same issue and this is the most helpful thread out there. My situation is a variant of the above and I will add my solution when I find it.

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

I am using an Access database for my data source and establish a querytable on a new sheet. I then add two more new sheets and try to establish a querytable using the same connection on each of them, but to a different Access table. The first querytable works just fine and I use .QueryTables(1).Delete and setting the querytable object to Nothing to make it disconnected.

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

However, the next sheet fails on establishing a new querytable using the same connection, which was not closed. I suspect (and will add the solution below) that I need to drop the connection before deleting the querytable. Rasmus' code above looks like the likely solution.

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