不需要使用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"
您还可以选择更新连接字符串。