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:…

Accepted Answer

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

Refer to QueryTable objects by name

Solution

Problem