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

Question

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

your question and the content seem to be contradicting. Server change or Database change? — Krishna, Jul 08 '15 at 15:13
Please do not use the [**macros** tag](http://stackoverflow.com/tags/macros/info) for MS Office / VBA. — Byron Wall, Jul 12 '15 at 02:54

Krishna · Accepted Answer · 2015-07-08T15:43:24.013

3

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

edited Jul 08 '15 at 15:43

answered Jul 08 '15 at 15:10

Krishna

2,451
1
26
31

I'll give that go @krishna and let you know if it works for me. Thank you. – TSQL_Newbie Jul 08 '15 at 15:20
Seems to run with no errors but the connections havent updated. – TSQL_Newbie Jul 08 '15 at 15:24
can you put a breakpoint on q.connection =... and see if the replace happened there correctly – Krishna Jul 08 '15 at 15:26
It doesnt seem to hit that line of code. When I watch through step through it jumps from Fro Each q.... down to Next... and goes through this process until its gone through each worksheet. – TSQL_Newbie Jul 08 '15 at 15:32
please try my EDIT- it worked on both sheet level query tables and connections in the workbook – Krishna Jul 08 '15 at 15:44
that works a treat. Thank you so much for your help. – TSQL_Newbie Jul 09 '15 at 07:46

score 1 · Answer 2 · answered Jul 08 '15 at 15:11

I would consider placing things like this in an sameNameAsWorkbook.ini file. Keep the file in the same location as the Workbook. Then have your code read the ini file and build the connection that way. Then if it needs to change you can change it in one spot very easily.

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

2 Answers2