Macro Error: Refreshing multiple worksheets using Power Query

[Sangar_338]

When I run a macro that refreshes a Power Query table that is itself the combination of four (4) other tables, whose data is imported from external sources, then the action does not complete, and I receive the following when I select to debug:

“Selection.ListObject.QueryTable.Refresh BackgroundQuery :=False” (see attached image)



To provide more detail, I have a worksheet that contains a table (my “master bible”, which is simply a database of orders) that consolidates four (4) separate tables each with their own worksheet, but that are combined in the same workbook, using Power Query. Each of these four (4) worksheets get their data from external sources, which are also Excel documents (namely “accessories”, “kids”, “ladies”, “mens”, which are the orders per their department).



Therefore, I have four (4) sheets in my workbook that contain imported data, in table format, from external workbooks (I used Power Query for this). These four (4) sheets are then combined into a single table in the sheet called “master bible” (I also used Power Query to do this).



I then run a macro that refreshes the four (4) tables that import data from external data sources, and that then also refreshes the table into which all this data is combined (the “master bible”).



This “master bible” is saved on a server. When I run the macro on my computer, it runs perfectly. When the macro is run on any other computer, it cannot complete the macro. When I debug the macro error it reads:

“Selection.ListObject.QueryTable.Refresh BackgroundQuery :=False” (see attached image)



I’m using Microsoft 365, Version 2012.

 

[arunsjain]

Try following:
Replace SheetName and query table name accordingly to run a specific query table.

ThisWorkbook.Worksheets("SheetName").ListObjects("query table name").QueryTable.refresh BackgroundQuery:=False