[SOLVED] Excel VBA change just the Server name in all data connections in a workbook

Excel VBA change just the Server name in all data connections in a workbook

Good Afternoon All,

I have been looking at various ways to change/update the data connection strings in a workbook to update the Server name only and keep the other parts of the string as they are.

Basically we have migrated servers and the server name has change and now we have a number of reports that need updating. Some have multiple connections as they connect to different databases.

I found a useful article but this replaces the string with a completely new one for all the data connections (which doesnt work due to the different databases) excel-macro-to-change-external-data-query-connections-e-g-point-from-one-data

Has anyone had to go through this process and found a way of easily updating the connection string?

Kind Regards

Solution

assuming the database names havent changed, but just the server, will the below work for you. Not tested

Sub ChangeAllConnections()

    Dim q As QueryTable, w As Worksheet, oldserverName As String, newServerName  As String
    Dim con As Variant

    oldserverName = "onlsrvr" 'Change here
    newServerName = "newsrvr" ' change here

    For Each w In ThisWorkbook.Worksheets
      For Each q In w.QueryTables
        q.Connection = Replace(q.Connection, oldserverName, newServerName)
      Next
    Next

    On Error Resume Next
     For Each con In ThisWorkbook.Connections
        con.ODBCConnection.Connection = Replace(con.ODBCConnection.Connection, oldserverName, newServerName)
        con.OLEDBConnection.Connection = Replace(con.OLEDBConnection.Connection, oldserverName, newServerName)
      Next
    On Error GoTo 0
End Sub