vba refresh query table

[DanielFran]

Hi,

I am trying to refresh a querytable using vba and the vba code i am using is the following:

Sub RefreshEmployee()
'


'
Sheets("Employee info").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False



End Sub

But I get an run-time error 91 (object variable or with block variable not set)

Not sure where I am going wrong

KR,
Dan

 

[Fazza]

hard to know without more information.

alternative that might be OK for you, might not, is to refresh all in the workbook

so if the code is in the workbook with the queries,
thisworkbook.refreshall

otherwise change the object reference (to the correct workbook)

HTH

 

[DanielFran]

Hi Fazza,

The workbook has five worksheets and one of the worksheets has the table query inside it. The worksheet name is "Employee info" and the table name is "Employee"

I have the code in a module

The only thing I wanted is to refresh the table, I tried with recording macro but doesn't work on the second time.

Let me know if I might have missed something

KR,
Dan

 

[Fazza]

hi, Dan

I'm not sure what you mean by doesn't work the second time. Maybe that it works when you record it (the first time) and then, after that, not when you run the code?

For info, the way to reference queries changed with Excel 2007. If you are pre Excel 2007 it is, in my mind, simpler. That just may be familiarity. From Excel 2007 list objects must be used.

See solutions at https://stackoverflow.com/questions/31476040/refresh-all-queries-in-workbook

If you really only wanted to restrict to a single worksheet, and assuming you are using Excel 2007 or newer, you could modify to

Sub maybe()


    Dim lob As Excel.ListObject
    
    'better to use the code name instead of worksheet name
    For Each lob In Worksheets("Employee info").ListObjects
        lob.QueryTable.Refresh BackgroundQuery:=False
    Next lob
    Set lob = Nothing


    
End Sub

 

[DanielFran]

Hi Fazza,

To your question, yes that is the case.

I tried your code and I get Run time error 91 - Object variable or with block variable not set

Note: I am using Excel 2016

Any thoughts?

KR,
Dan

 

[Fazza]

check you have a worksheet called "Employee Info"

 

[DanielFran]

Hi Fazza,

Yes, I have a worksheet with that name 'Employee Info'

 

[Fazza]

What code does the macro recorder create (on the version of Excel you're asking about)?

 

[Fazza]

Dan,

Feels like I haven't got the full picture of what is happening. And I can't test in Excel 2016. So thinking I can't help; much more on this. Suggest again using workbookreference.refreshall

Or otherwise google. If that doesn't resolve, suggest starting a new thread with really specific description of the situation.

regards, Fazza