通过VBA更新ODBC excel链接上的路径

Question

我有一个当前的ODBC链接创建了一段时间后，查询Excel文件。我现在想做的是通过工作簿中的每个ODBC连接并更新连接字符串，以便它使用不同的路径，其中另一个同名的.xls文件位于。

换句话说，我在Excel中看到的当前连接字符串(数据>连接>连接>1stConn属性>定义选项卡>连接字符串)如下:

DSN=Excel Files;DBQ=C:TESTCurrentQuarter.xls;DefaultDir=C:TEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

，我想把它改成:

DSN=Excel Files;DBQ=C:OTHERTESTCurrentQuarter.xls;DefaultDir=C:OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

我试过这个代码:

Sub SwitchODBCSource()
    Dim conn As WorkbookConnection
    For Each conn In ActiveWorkbook.Connections
        With conn
            'I have tried without succes the following 2 properties, without any luck:
            .CommandText = "DSN=Excel Files;DBQ=C:OTHERTESTCurrentQuarter.xls;DefaultDir=C:OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
            .Connection = "DSN=Excel Files;DBQ=C:OTHERTESTCurrentQuarter.xls;DefaultDir=C:OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
        End With
    Next conn
    Set conn = Nothing
End Sub

我是否在连接(.CommandText或.Connection)上使用适当的方法?我有一种感觉，我不是因为VBA抛出我一个错误"对象不支持此属性或方法"

在这种情况下，使用的对象是QueryTable。我应该改变对象并使用那个吗?我的印象是那个用户不想连接到一个。xls文件。

任何帮助将非常感激!

Answer 1

尝试conn.ODBCConnection或conn.OLEDBConnection，因为它们确实具有.commandtext和.connection的属性。

我不知道这是否允许你改变它们。我本以为您需要删除连接，然后使用新的连接字符串重新创建连接。

Answer 2

你是正确的:connection . odbcconnection . connection是正确的。它让我改变它与VBA!太棒了。

这是一个慷慨的MrExcel研究员建议的代码，工作良好(谢谢Jerry):

Sub SwitchODBCSource()
Dim conn As WorkbookConnection
Dim sOldConnection As String, sNewConnection As String
Const sOldPath As String = "C:TEST" '--omit trailing backslashes to change DefaultDir
Const sNewPath As String = "C:OTHERTEST"
For Each conn In ActiveWorkbook.Connections
    With conn
        If .Type = xlConnectionTypeODBC Then
            sOldConnection = .ODBCConnection.Connection
            If InStr(1, sOldConnection, sOldPath) > 0 Then
                sNewConnection = Replace(sOldConnection, _
                        sOldPath, sNewPath, Compare:=vbTextCompare)
                .ODBCConnection.Connection = sNewConnection
                .Refresh '--optional to refresh now
            End If
        End If
      End With
Next conn
Set conn = Nothing
End Sub

谢谢尼克

Sebastien