Updating paths on ODBC excel links through VBA

Question

I have a current ODBC link created a while back that is querying an Excel file. What I would like to do now is to through each ODBC Connection in the workbook and update the Connection String so it uses a different path where another .xls file of the same name is located.

In other words, the current connection string as I see it in Excel (Data>Connections>Connections>1stConn Properties>Definition tab>Connection String) is the following:

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

and I wish to change it to:

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

I have tried this code:

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:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
            .Connection = "DSN=Excel Files;DBQ=C:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

        End With
    Next conn

    Set conn = Nothing

End Sub

Am I using an appropriate method on Connection ( .CommandText or .Connection) ? I have the feeling that I am not since VBA throws me an error "Object doesn't support this property or method"

In that case, the Object used is a QueryTable. Should I change Object and use that one ? I was under the impression that that user didn't want to connect to an .xls file..

Any Help would be greatly appreciated !

Answer 1

try conn.ODBCConnection or conn.OLEDBConnection as they do have the .commandtext and .connection properties.

I've got no idea if this will allow you to change them though. I would have thought that you need to remove and then re-create the connections using the new connections string.

Answer 2

you are correct: conn.ODBCConnection.Connection is the way to go. And it is letting me change it with VBA! Awesome.

Here is the Code that a generous MrExcel fellow suggested which is working fine (thanks 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

Thanks Nick

Sebastien