Updating paths on ODBC excel links through VBA

[MrSebas]

Hi All,

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
[FONT=arial black][I]                    'I have tried without succes the following 2 properties, without any luck:[/I][/FONT]
                         .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"

I have seen a similar question asked on MrExcel before : http://www.mrexcel.com/forum/excel-...ns-used-set-odbc-connection-string-query.html

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 !

 

[Jerry Sullivan]

Hi and Welcome to the Board,

You'll want to set the .ODBCConnection.Connection Property of the Connection (conn) object.

This seems to work provided that the new connection file exists and can be connected.

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

I hadn't tried this before and there's probably better code out there that handles the exception of the new connection file not existing. If someone doesn't offer that, I'll try to cobble that together tomorrow.

 

[MrSebas]

Hi Jerry,

Works great!

But I don't have an exception thrown for the sNewConnection not existing? I don't understand where the concern is

sNewConnection is an empty string then the Replace Method sets it to "ODBC;DSN=Excel Files;DBQ=C:\OTHERTEST\CurrentQuarter.xls;DefaultDir=C:\OTHERTEST;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Am I not seeing something here?

Thanks!

Sebastien

 

[Jerry Sullivan]

Hi Sebastien,

My concern is that if the file C:\OTHERTEST\CurrentQuarter.xls doensn't exist, the connection will be remapped there, and the code will error when trying to .Refresh. That could be addressed by testing for the existence of the file first before Remapping or .Refreshing . The user might prefer not to remap at all if the file doesn't exist.

Similarly, if the file exists but Excel can't connect to it because it's on an inaccessible server, the remap could be done but the refresh will fail.

Lastly, and more difficult to test before remapping is that the data fields could be different in the newly mapped file, in which case the refresh could throw an error due to an invaid query.

I think the simplest approach to address all of these would be to:
Suppress alerts;
Remap and try to refresh the connnection;
Test if the connection was successful
If not, remap to the old path and provide a msgbox alert

Someone else has probably addressed the issues of testing for a valid connection and VBA handling when refreshing an invalid connection or query. You might be able to find an example through web search - otherwise I'll look at this later today.