Change ODBC CommandText using Loop

[mountainredrockdog]

I have a spreadsheet which includes multipe queries that connect to the same "live" database via an ODBC connection. There are multiple sheets in the workbook; each sheet pulls a different query from the same "live" database using the same ODBC connection.

My goal is to allow a user to toggle between the "live" and "beta" databases via a macro. Specifically: I have successfully created VBA to change the ODBC connection string (i.e. old string with new string) for each query on each sheet and it functions properly. I would now like to use VBA to loop through all sheets and replace the word "live.dbo" with "beta.dbo" in the query command text for each query or queries included on each sheet. Is this possible?

 

[John_w]

What's the error? Always state the error.

An error on Set qt = lo.QueryTable suggests the ListObject (table) doesn't have a QueryTable associated with it and therefore the table is from a different type of query. This modified version of the original macro changes the CommandText only if the ListObject's SourceType is a QueryTable.

Public Sub Change_Command_Text()

    Dim ws As Worksheet, lo As ListObject, qt As QueryTable, prev As String
    
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            If lo.SourceType = xlSrcQuery Then
                Set qt = lo.QueryTable
                prev = qt.CommandText
                qt.CommandText = Replace(qt.CommandText, "table1Name.", "newTable1Name.", compare:=vbTextCompare)
                qt.CommandText = Replace(qt.CommandText, "table2Name.", "newTable2Name.", compare:=vbTextCompare)
                MsgBox "Previous CommandText: " & prev & vbCrLf & "New CommandText: " & qt.CommandText
            End If
        Next
    Next
   
End Sub

However, the next macro might work better for you. It changes the CommandText of all ODBC workbook connections for all sheets in the workbook.

Public Sub Change_Command_Text_ODBC_Workbook_Connections()

    Dim ws As Worksheet
    Dim wbConnection As WorkbookConnection
    Dim destRange As Range, destRanges As String
    
    For Each ws In ThisWorkbook.Worksheets
    
        For Each wbConnection In ActiveWorkbook.Connections
            
            With wbConnection
            
                destRanges = ""
                For Each destRange In .Ranges
                    destRanges = destRanges & destRange.Worksheet.Name & " " & destRange.Address & "; "
                Next
                destRanges = Left(destRanges, Len(destRanges) - 2)
            
                If .Type = xlConnectionTypeODBC Then
                     If Not .ODBCConnection Is Nothing Then
                         MsgBox "Destination range(s) = " & destRanges & vbCrLf & vbCrLf & _
                                "Connection Name = " & .Name & vbCrLf & _
                                "Connection = " & .ODBCConnection.Connection & vbCrLf & _
                                "CommandText = " & .ODBCConnection.CommandText, _
                                Title:="ODBC Connection Properties"
                         .ODBCConnection.CommandText = Replace(.ODBCConnection.CommandText, "table1Name.", "newTable1Name.", Compare:=vbTextCompare)
                         .ODBCConnection.CommandText = Replace(.ODBCConnection.CommandText, "table2Name.", "newTable2Name.", Compare:=vbTextCompare)
                         MsgBox "New CommandText = " & .ODBCConnection.CommandText, Title:="ODBC Connection Properties"
                    End If
                End If
            
            End With
            
        Next
        
    Next
   
End Sub