Excel macro to change external data query connections - e.g. point from one database to another

Question

I'm looking for a macro/vbs to update all the external data query connections to point at a different server or database. This is a pain to do manually and in versions of Excel before 2007 it sometimes seems impossible to do manually.

Anyone have a sample? I see there are different types of connections 'OLEDB' and 'ODBC', so I guess I need to deal with different formats of connection strings?

Answer 1

Connection string format is largely irrelevant as Excel will pass it to data providers.

Update one querytable manually, then do something like this:

dim w as worksheet, q as querytable

for each w in thisworkbook.worksheets
  for each q in w.querytables
    q.connection = SampleSheet.querytables("PreparedQueryTable").connection
  next
next

Answer 2

I ended up writing the following, which prompts for the connection details, creates a connection string, then updates all external data queries to use that connection string.

'''' Prompts for connection details and updates all the external data connections in the workbook accordingly.
'''' Changes all connections to use ODBC connections instead of OLEDB connections.
'''' Could be modified to use OLEDB if there's a need for that.
Sub PromptAndUpdateAllConnections()
    Dim Server As String, Database As String, IntegratedSecurity As Boolean, UserId As String, Password As String, ApplicationName As String
    Dim ConnectionString As String
    Dim MsgTitle As String
    MsgTitle = "Connection Update"

    If vbOK = MsgBox("You will be asked for information to connect to the database, and this spreadsheet will be updated to connect using those details.", vbOKCancel, MsgTitle) Then
        Server = InputBox("Database server or alias and instance name, e.g. 'LONDB01' or 'LONDB01\INST2'", MsgTitle)
        If Server = "" Then GoTo Cancelled
        Database = InputBox("Database name", MsgTitle, "a default value")
        If Database = "" Then GoTo Cancelled
        IntegratedSecurity = (vbYes = MsgBox("Integrated Security? (i.e. has your windows account been given access to connect to the database)", vbYesNo, MsgTitle))
        If Not IntegratedSecurity Then
            UserId = InputBox("User Id", MsgTitle)
            If UserId = "" Then GoTo Cancelled
            Password = InputBox("Password", MsgTitle)
            If Password = "" Then GoTo Cancelled
        End If
        ApplicationName = "Excel Reporting"

        ConnectionString = GetConnectionString(Server, Database, IntegratedSecurity, UserId, Password, ApplicationName)
        UpdateAllQueryTableConnections ConnectionString
        MsgBox "Spreadsheet Updated", vbOKOnly, MsgTitle
    End If
    Exit Sub
Cancelled:
    MsgBox "Spreadsheet not updated", vbOKOnly, MsgTitle
End Sub

'''' Generates an ODBC connection string from the given details.
Function GetConnectionString(Server As String, Database As String, IntegratedSecurity As Boolean, _
    UserId As String, Password As String, ApplicationName As String)

    Dim result As String

    If IntegratedSecurity Then
        result = "ODBC;DRIVER=SQL Server;SERVER=" & Server & ";DATABASE=" & Database _
                & ";Trusted_Connection=Yes;APP=" & ApplicationName & ";"
    Else
        result = "ODBC;DRIVER=SQL Server;SERVER=" & Server & ";DATABASE=" & Database _
                & ";UID=" & UserId & ";PWD=" & Password & ";APP=" & ApplicationName & ";"
    End If

    RM_GetConnectionString = result
End Function

'''' Sets all external data connection strings to the given value (regardless of whether they're
'''' currently ODBC or OLEDB connections. Appears to change type successfully.
Sub UpdateAllQueryTableConnections(ConnectionString As String)
    Dim w As Worksheet, qt As QueryTable
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection
            odbcCn.SavePassword = True
            odbcCn.Connection = ConnectionString
        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.SavePassword = True
            oledbCn.Connection = ConnectionString
        End If
    Next
End Sub