.ListObject.QueryTable.Refresh triggers error 91

[PJDMrEx]

Hi everyone,
I have an excel workbook with 2 powerquery queries that produce 2 tables named "success" and "Fail".
I have 2 buttons which associated codes are

Private Sub Fail_Refresh_Click()
Application.Range("Fail").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Private Sub Success_Refresh_Click()
Application.Range("success").ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Clicking on success works, clicking on fail returns "Error 91: Object Variable or With Block Variable Not Set"

Any idea what I should check ?

Thank you
Ph.Drouhin
Office 365

 

[Micron]

In cases like this I often use the immediate window to vet the hierarchy of objects by invoking the Name property since just about every object has a name.

?Application.Range("success").Name

Include ? and hit enter at the end of the line. If OK, you'll get the name of the referenced object or property, depending on what you're testing. If it errors, then you know at least one part that is causing trouble. Could be as simple as case sensitivity. If one part works, add more and test again:

?Application.Range("success").ListObject.QueryTable.Name

and so on. In this case I'd say there's nothing left to test after that. You can also know what any property value is by using the property name. It doesn't have to be "Name".