Working with Query Table in Excel/VBA

Question

I am getting a "Subscript out of Range" Error 9 with my code. I have a Microsoft Access database connected to Excel and a table already made with the Query for Table "QUERY_1". I want to update the table with "QUERY_2" through using VBA. The error occurs on this line:

Set QueryARV = QuerySheet.QueryTables(1)

Here is the full procedure:

Sub ChooseQueryTable()

Dim QueryARV As QueryTable
Dim QuerySheet As Worksheet

Set QuerySheet = ThisWorkbook.Sheets("Sheet1")
Set QueryARV = QuerySheet.QueryTables(1)

With QueryARV
.CommandType = xlCmdTable
.CommandText = "QUERY_1"
.Refresh
End With

End Sub

Giving me back a 0. I have tried using QueryTables("Table Name"), but no success with that either. — RJW
– RJW, Commented Jan 20, 2015 at 19:48
I may be calling a Query Table incorrectly? I connected Access and Excel and in the Command Text have a table named "QUERY_1". I want to be able to change that name to "QUERY_2". Is that not done through using the QueryTable function? — RJW
– RJW, Commented Jan 20, 2015 at 19:52
"subscipt out of range" from QuerySheet.QueryTables(1) made me suspect QuerySheet.QueryTables.Count is zero. But I've never actually used QueryTables, so can't suggest how to fix it. — HansUp
– HansUp, Commented Jan 20, 2015 at 19:57
I think I have found a solution by using Querysheet.ListObject.Item(1).QueryTable. I am unsure why it needs to be called a List Object item first, but I also do not have much expereince with this. Thank you. — RJW
– RJW, Commented Jan 20, 2015 at 20:52

Alexander Bell · Accepted Answer · 2015-01-20 19:26:16Z

0

In a newly created MS Excel Workbook by default you have only 3 Worksheets, so pertinent to your case, either change the ThisWorkbook.Sheets("Sheet4") to ThisWorkbook.Sheets("Sheet3") , or add another Worksheet to make sure that Sheet4 actually exists - issue resolved

I would also recommend to modify your code by adding proper error handling as shown below:

Sub ChooseQueryTable()
    Dim QueryARVIS As QueryTable
    Dim QuerySheet As Worksheet
On Error GoTo err:
    Set QuerySheet = ThisWorkbook.Sheets("Sheet1")

    If (QuerySheet.QueryTables.Count > 0) Then
        Set QueryARVIS = QuerySheet.QueryTables(1)
        With QueryARVIS
            .CommandType = xlCmdTable
            .CommandText = "10K_WELLCOUNT_PERMIAN_ID"
            .Refresh
        End With
    End If
    Exit Sub
err:
    MsgBox (err.Description)
End Sub

Please make sure that QuerySheet.QueryTables(1) actually exists (most likely it doesn't). Hope this will help. Best regards

edited Jan 20, 2015 at 19:26

answered Jan 20, 2015 at 19:09

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

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

1 Comment

RJW Over a year ago

I should specify, I do have 3 other Sheets in this Workbook. I have edited the post to say "Sheet1" for simplicity however.

Collectives™ on Stack Overflow

Working with Query Table in Excel/VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related