Change all Workbook Connection Strings

[NavyET]

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.

[Jan Karel Pieterse]

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):

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

[NavyET]

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"

[NavyET]

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

[Jan Karel Pieterse]

Glad to hear you were able to sort things out!