Macro Error: Refreshing multiple worksheets using Power Query

Sangar_338

New Member
Joined
Oct 21, 2019
Messages
2
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.
 

Attachments

  • ERROR 2_ORDERS.png
    ERROR 2_ORDERS.png
    123.1 KB · Views: 7

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

arunsjain

Board Regular
Joined
Apr 29, 2016
Messages
102
Try following:
Replace SheetName and query table name accordingly to run a specific query table.

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

Watch MrExcel Video

Forum statistics

Threads
1,127,619
Messages
5,625,891
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top