在Excel 2010中使用VBA与VBA创建多个连接的查询表（QueryTables）

问题描述：

我正在关注在另一个站点上找到的代码。这里是我的代码的基础：在Excel 2010中使用VBA与VBA创建多个连接的查询表（QueryTables）

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 连接2

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

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

答

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

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

如果您需要更改基础SQL语句，您有一些选项。您可以设置提示输入值的参数或从单元格获取值。另一个更改SQL的选项是将其更改为现有QueryTable的代码。

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

您可以通过更改CommandText来选择不同的列或甚至不同的表。如果它是一个不同的数据库，你需要一个新的连接，但这是非常罕见的。

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

有关参数的更多信息，请参阅http://www.dicks-clicks.com/excel/ExternalData6.htm这是2003年的版本，因此与更高版本的不一致性很少。基础是一样的，如果你使用2007或更高版本，你可能需要了解ListObject对象。

答

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

我没有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

当我尝试时，Worksheets（“Received”）...行给出了一个无效的过程调用运行时错误5.我也尝试将它从新连接更改为新的ADODB连接并相应地使用它。没有骰子。 – DavidStein 2011-02-08 14:46:58

答

我有同样的问题。前一个答案，而正确的方向是PITA。

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

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

即对现有的QueryTable对象只是这样做：

.MaintainConnection = False

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

答

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

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

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

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

答

如果你想删除，如果刷新之后，你应该做的刷新不是在后台（使用第一个参数 - >刷新假），这样你的动作正确顺序

答

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

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

答

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

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

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

答

而不是使用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"

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

在Excel 2010中使用VBA与VBA创建多个连接的查询表（QueryTables）

相关推荐