Consulting

Results 1 to 5 of 5

Thread: Change all Workbook Connection Strings

  1. #1
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location

    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.

  2. #2
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location
    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"

  4. #4
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    3
    Location
    UPDATE: Added ODBC; to the front of the connection string and working perfectly. Thank you to Jan Karel Pieterse for saving the day.

  5. #5
    Glad to hear you were able to sort things out!
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •