excel - Refer to QueryTable objects by name

Question

I am developing a MS Excel 2013 tool with VBA, which involves the use of QueryTables.

One inconvenience is accessing existing QueryTables within an Excel worksheet. Currently, the only method I can find to access a query table is by integer indexing. I came up with the following code for a quick proof of concept:

Sub RefreshDataQuery()

Dim querySheet As Worksheet
Dim interface As Worksheet

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")

Dim sh As Worksheet
Dim QT As QueryTable

Dim startTime As Double
Dim endTime As Double

Set QT = querySheet.ListObjects.item(1).QueryTable

startTime = Timer
QT.Refresh
endTime = Timer - startTime

interface.Cells(1, 1).Value = "Elapsed time to run query"
interface.Cells(1, 2).Value = endTime
interface.Cells(1, 3).Value = "Seconds"

End Sub

This works, but I don't want to do it this way. The end product tool will have up to five different QueryTables. I want to refer to a QueryTable by its name.

How could I translate:

Set QT = querySheet.ListObjects.item(1).QueryTable

To something along the lines of:

Set QT = querySheet.ListObjects.items.QueryTable("My Query Table")

Answer 1

In Excel 2003 and prior, an external data connection would create a QueryTable object whose parent was a worksheet. You could access the QueryTable object, for one, through the QueryTables collection object. Like most collection objects, you can pass an index number or a name to the (default) Item method to get it.

Sheet1.QueryTables("MyQtName")

When you open a 2003 worksheet in a new version, it still has a QueryTable object and can be accessed the same way. Even if you convert the file format, the QueryTable persists.

In 2007 and later versions, there are only three ways to create a QueryTable that will be a member of Worksheet.QueryTables:

1. Through code
2. Data - From Text
3. Data - From Web

All other UI external data connections in these new versions will result not in a QueryTables member, but in a ListObject. That ListObject will have one and only one QueryTable object that can be accessed via the ListObject.QueryTable property.

Here's the bad news. The QueryTable whose parent in a ListObject doesn't have a Name property. Well, it's there, but you will get a runtime error 1004 if you try to access it. I guess MS decided since there's only one QueryTable per ListObject, it didn't make sense that it should have a name.

If you try to convert a Worksheet.QueryTables.QueryTable into a ListObject, the external data connection goes away and the new ListObject doesn't have a QueryTable.

Since your QueryTables.Count is returning zero, all of your QueryTables are inside ListObjects and don't have names. The ListObjects have names. You can use

Sheet1.ListObjects("MyListName").QueryTable

Here's a function that takes a name and a worksheet and returns a QueryTable that either has that name or is a child of a ListObject that has that name.

Public Function QueryTableByName(ByVal sName As String, ByRef sh As Worksheet) As QueryTable

    Dim qt As QueryTable
    Dim lo As ListObject

    On Error Resume Next
        Set qt = sh.QueryTables(sName)
    On Error GoTo 0

    If qt Is Nothing Then
        On Error Resume Next
            Set lo = sh.ListObjects(sName)
        On Error GoTo 0

        If Not lo Is Nothing Then
            On Error Resume Next
                Set qt = lo.QueryTable
            On Error GoTo 0
        End If
    End If

    Set QueryTableByName = qt

End Function