VBA ListObject..Refresh BackgroundQuery:=False not working in Excel O365 (version 1808)

Question

I recently upgraded from Excel 2013 to O365 Version 1808 (Build 10730.20262) - https://docs.microsoft.com/en-us/officeupdates/semi-annual-channel-targeted-2018.

I am now unable to use the VBA code  ListObject..Refresh BackgroundQuery:=False  to refresh queries in background without changing the query properties.

As a test, I have created a query with the following M code:

// Query1

#table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})

I have tried to refresh this with the below VBA:

Sub test()

    Dim tbl As ListObject

    Set tbl = ActiveWorkbook.ActiveSheet.ListObjects("Query1")

    tbl.Refresh BackgroundQuery:=False

End Sub

The error message received is:

   Compile error: Wrong number of arguments or invalid property assignment.

Any suggestions? This appears to be a bug with Excel as I cannot find any notes that the property was removed.

Answer 1

Hi Andrew

Sub test()

    Dim tbl As ListObject

    Set tbl = ActiveWorkbook.ActiveSheet.ListObjects("Query1")

    tbl**.QueryTable**.Refresh BackgroundQuery:=False

End Sub

works better?

Answer 2

You're welcome & Thanks for posting back

Answer 3

Perfectly, thank you.