Excel VBA refer to QueryTable objects by name

calendar_today Asked Aug 5, 2013
thumb_up 5 upvotes
history Updated April 16, 2026

Question posted 2013 · +10 upvotes

I am developing a MS Excel 2013 tool with VBA which involves the use of QueryTables. One inconvenience I am experiencing 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 really don’t want to do it this way. The end product tool will have up to five different QueryTables. What I want is to refer to a QueryTable by its name.

What would be nice is if I could translate the code below 

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

To something along the lines

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

Any suggestions would be much appreciated.

Accepted answer +5 upvotes

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")

3 code variants in this answer

  • Variant 1 — 1 lines, starts with Set QT = querySheet.ListObjects.items(1).QueryTable
  • Variant 2 — 3 lines, starts with Dim LS as ListObject
  • Variant 3 — 1 lines, starts with Set QT = Worksheet("QTable").QueryTables("My Query Table")

Excel VBA objects referenced (4)

  • LS.QueryTable — Using events with the QueryTable object
  • LS.QueryTable — QueryTable object events
  • Worksheet — Refer to All the Cells on the Worksheet
  • Worksheet — List of worksheet functions available to Visual Basic

Top excel-vba Q&A (6)

+5 upvotes ranks this answer #124 out of 136 excel-vba solutions on this site .
excel-vba