Why Excel VBA is not finding my Query Table?

Question

I have a query table in a worksheet, which is imported from another excel file (using the Power Query Editor). The worksheet is the only one in the workbook.

In a VBA procedure inside This workbook module, I try to assign my querytable to a variable:

Private WithEvents QT As QueryTable
Set QT = Activesheet.QueryTables(1)

But I get the error “subscript out of range”. After some investigation, I tried to count the number of querytables in my worksheet:

MsgBox ActiveSheet.QueryTables.Count

and the result was 0 (zero). Therefore, the VBA can’t “find” my table. What am I doing wrong? I am new in the query tables world, so any help is very welcome.

Michal · Accepted Answer · 2025-04-14 10:13:06Z

2

When data is imported using the Power Query Editor, Excel loads the result into a table (a ListObject) rather than as a QueryTable. Instead you can use the following:

Private WithEvents QT As QueryTable

Sub InitializeAndManipulateQueryTable()

    Dim lo As ListObject
    
    Set lo = ActiveSheet.ListObjects(1)
    Set QT = lo.QueryTable
    
End Sub

answered Apr 14 at 10:13

Michal

7,6894 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why Excel VBA is not finding my Query Table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related