VBA code to list all the queries in an Excel workbook

Public Sub ListQueries()
    Dim ws As Worksheet
    Dim q As Worksheet
    Dim qt As QueryTable
    Dim r As Integer
    
    Set q = Worksheets.Add
    With q
        .Range("A1") = "Worksheet"
        .Range("B1") = "Destination"
        .Range("C1") = "Query Name"
        .Range("D1") = "Connection String"
        .Range("E1") = "Query"
    End With

    r = 2
    For Each ws In Worksheets
        If ws.QueryTables.Count > 0 Then
            For Each qt In ws.QueryTables

                q.Cells(r, 1) = ws.Name
                q.Cells(r, 2) = qt.Destination.Address
                q.Cells(r, 3) = qt.Name
                q.Cells(r, 4) = qt.Connection
                q.Cells(r, 5) = qt.CommandText
    
                r = r + 1
            Next
        End If
    Next
End Sub
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s