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
        End If
End Sub

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s