Change all Workbook Connection Strings

I'm fairly new to VBA and have searched for my problem with no solution so now I'm asking the experts. My workbook has a connection in each worksheet built in Microsoft Query to return my desired results. Each Connection String is the same with varying Command Text from SQL. I'm trying to loop through each worksheet to point to a different Server name for each connection. The current connection string example is: "Driver=SQL Server;SERVER=OldServer;UID=User;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=Workstation1"

I'm only trying to change SERVER=OldServer to SERVER=NewServer or even better if the server name can be typed in a cell and be referenced to update connection strings.

Any help is greatly appreciated.

Suppose you have a worksheet called "Settings" which contains a named cell "VBA.ConnectionString" which contains your connection string. Then this code should do the trick (not tested):

Code:

---

Sub UpdateConnections()
    Dim sConn As String
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim oQ As QueryTable
    sConn = ThisWorkbook.Worksheets("Settings").Range("VBA.ConnectionString").Value
    For Each oSh In Worksheets
        For Each oQ In oSh.QueryTables
            oQ.Connection = sConn
        Next
        For Each oLo In oSh.ListObjects
            oLo.QueryTable.Connection = sConn
        Next
    Next
End Sub

---

Thank you for the quick response. I've created a worksheet named "Settings" and a named cell A1 "VBA.ConnectionString". When running I get "Run-time error '1004': Application-defined or object-defined error" for line "oLo.QueryTable.Connection = sConn"

UPDATE: Added ODBC; to the front of the connection string and working perfectly. Thank you to Jan Karel Pieterse for saving the day.

Glad to hear you were able to sort things out!