Refer to QueryTable objects by name

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

This works:

Range("ClassifiedAsSupply").ListObject.QueryTable.Refresh BackgroundQuery:=True

Thanks to Dingbatdata: https://www.dingbatdata.com/2017/11/24/referencing-listobjects-with-vba/#comment-719


According to this MSDN link for ListObject there isn't any collection of QueryTables being a property of ListObjects. Correct code is:

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

What you possibly need is to refer to appropriate ListObject item like (just example code):

Dim LS as ListObject
Set LS = querySheet.ListObjects("My LO 1")
Set QT = LS.QueryTable

The other alternative is to refer to QT through WorkSheet property in this way:

Set QT = Worksheet("QTable").QueryTables("My Query Table")

Tags:

Excel

Vba

Recent Posts