The following VBA Macro will change the connection properties of all the Power Query connections to enable or disable background refresh.
Sub Change_Background_Refresh() 'Description: Enable or disable background refresh on all Power Query connections 'Author: Jon Acampora, Excel Campus 'Source: https://www.excelcampus.com/vba/enable-background-refresh-on-all-power-query-connections/ Dim lCnt As Long 'The following code loops through all connections 'in the active workbook. Change the property to 'True to Enable, False to Disable background refresh. With ActiveWorkbook For lCnt = 1 To .Connections.Count 'Excludes PowerPivot and other connections If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then .Connections(lCnt).OLEDBConnection.BackgroundQuery = False End If Next lCnt End With End Sub
Disabling background refresh allows the query to complete the refresh before the other queries are refreshed. It also allows all queries to be refreshed before the pivot tables are refreshed. This is important if your workbook contains pivot tables that use a Power Query output table as the source data range.
When clicking the Refresh All button on the Data tab, we typically first want to refresh the queries (Tables) before refreshing the pivot tables. Disabling the background refresh of the connections on the Connection properties window makes this possible.
Refresh All Queries and Then Refresh All Pivot Tables
The macro above only needs to be run once to change the properties of the connections. The properties will be saved and stored with the workbook.
When pressing the Refresh All button or running the following line of code, the Power Query connections (Tables) will be refreshed before the pivot tables are refreshed in the workbook.
This allows you or the user to refresh all the queries and pivot table reports with the press of a button.