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.
ActiveWorkbook.RefreshAll
This allows you or the user to refresh all the queries and pivot table reports with the press of a button.
My friend Celia Alves wrote a great post on some additional ways to make VBA wait for Power Query if you are triggering the refreshes with macros.
Related Articles
Fill Down Table Formulas after Power Query Refresh
Hello
Great tip! Saves me a lot of time 😉
BTW Do you know how to disable “Refresh this connection on Refresh All” on All Power Query Connections using VBA?
Thanks!
Thanks Jorge! Great question! Yes, you can enable or disable that setting “Refresh this connection on Refresh All” with the RefreshWithRefreshAll property. It is a member of the WorkbookConnection.
In the following code I added the line for RefreshWithRefreshAll. This will disable or turn off the setting. Change the property to True to turn it back on for all connections in the workbook.
With ActiveWorkbook
For lCnt = 1 To .Connections.Count
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
.Connections(lCnt).RefreshWithRefreshAll = False
Next lCnt
End With
I hope that helps. Thanks again! 🙂
If deploying to the masses a Power Pivot using Power Query to load the data model what are all the connections/settings that need to be disabled (and how) so the PQ doesn’t try to reload the data which is stored locally on my PC. Is the above code all I need to worry about?
Also, I assume saving this code in the workbook will require an xlsm extension. If this Power Pivot gets refreshed monthly, is there an automated way to run externally where I can keep the file an xlsx?
Thank you,
Eric
Hi Eric,
You can refresh the PowerPivot connections only with the following line of code.
You can change that code to reference the name of the xlsx workbook, instead of the ActiveWorkbook, so you don’t have to save the code in the workbook.
Workbooks("Erics Reports.xlsx").Model.RefreshThe workbook will have to be open before you can run that line of code. You can also open the workbook with VBA code. Just replace the file path with the actual file path and file name.
Workbooks.Open "C:/Docs/Erics Reports.xlsx" Workbooks("Erics Reports.xlsx").Model.RefreshI hope that helps.
Hello Jon
Great tuto that I keep using in many of my table score cards !
I have recently faced an issue, using your refresh code.
I get a vba error message that occurs when trying to refresh the 15 tables of my Power Query workbook.
The error occurs on the last connection when trying to refresh the “workbookdatamodel”.
If I change your loop to “For lCnt = 1 To .Connections.Count -2”, everything works fine and I have no error.
What could be wrong ?
thanks
Hi Patrice,
I’m sorry to not reply sooner. Thank you for letting me know about this. I updated the code to exclude the PowerPivot connections. That way you don’t have to change loop. I hope that helps. Thanks again! 🙂
Hi Jon, how is the code so it doesn´t exclude PowerPivot?
Thanks 🙂
Hi Jon,
I would like to know how I should use the existing excel workbook when external data is updating data in other worksheets? I always need to wait until it completes in 15-20 seconds.
Thanks
Hello,
This is VERY intriguing indeed, but I am new to Power Query, and I am having sudden huge problems with speed and have seen people elsewhere talk about disabling the background refresh to help, but the one time I tried it there were errors everywhere for my queries, so I closed it without saving. Do you know if you can disable background refresh on connection only queries, and if so, is there an order you have to do it in, can you do it in the general settings, or will this macro set it up? Like I said, I am new to Power Query and trying hard to learn the ropes. I am using Excel 2019, 64Bit, with 16 Gigs RAM, but my CPU is getting taxed out.
Thank you if you can advise,
Maggie
Hi Maggie, this is the code that I using…see this function and extract the part that you need for your purpose
Public Function takedata()
Dim cnn As WorkbookConnection
Dim conection_name As String
Dim myPivotTable As PivotCache
Application.ScreenUpdating = False
On Error GoTo ctrl_error
For Each cnn In ActiveWorkbook.Connections
‘Debug.Print cnn.Name
If Right(cnn.Name, 2) = “50” Then ‘put special attention in this zone
conection_name = cnn.Name
With ActiveWorkbook.Connections(conection_name).OLEDBConnection
.BackgroundQuery = False
.refresh
End With
End If
Next
‘ahora aprovecho de refrescar las pivotTables
For Each myPivotTable In ThisWorkbook.PivotCaches
myPivotTable.refresh
Next myPivotTable
Worksheets(“Trial Balance”).Activate
Range(“A1”).Activate
MsgBox “Los datos se actualizaron correctamente”, 64, “@Mikel ERP”
ctrl_error:
Select Case Err.Number
Case 0
Case 1004
MsgBox “El sistema no se pudo conectar al origen de datos.” & Chr(10) & _
“” & Chr(10) & _
“Contacte al administrador de la red !!!”, vbExclamation, “@Mikel ERP”
Case Else
MsgBox “Contacte al personal asesor”, vbExclamation, “@Mikel”
End Select
Application.ScreenUpdating = True
End Function
I hope help you
Best regards, from Maracaibo-Venezuela
Hi,
I would like to ask you if i can refresh only specific powerpivot table in the Data Model.
This is used only as allias table and it is only in the Data model not in sheets.
Thanks a lot
Thank you Jon, fantastic code, it really has made a huge difference to me. Is it possible to run a macro that can disable background refresh on all power query connections on all workbooks in a folder, rather than having to run the macro on each individual file? Thank you.
I’ve used this successfully many times in the past, but for some reason now I’m getting a Invalid procedure call or argument error on this line .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
Using Excel 365. Help?
I got here via your youtube video “Refresh Pivot Table Automatically when Source Data Changes,” which was very helpful. Here are two tweaks for that.
By adding a formula to a cell on the data sheet =COUNTA(MyTableName) you can trigger a Worksheet_Calculate event whenever the table changes in any way. This event doesn’t interfere with undo/redo.
If you set a flag variable instead of refreshing on every change, you can defer the pivtotcache refresh until all changes are complete, which should mitigate the performance problems some are reporting.
Hi Jon,
Your articles are a great help to me. Thank you!
I’ve a question, please:
How can I use your code for refreshing the source data (PQ through SQL) first before refreshing the Pivot Tables (coming from Datamodel, and also stand alone), when I have the refresh data when file is opened activated?:
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/library/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
It works very well when I manually click on the refresh all button. But when I close and reopen the file, in order for it to refresh the source data automatically, nothing happens.
Can you please help?
Hi John
I tried using this on a cell that had a nested XLOOKUP – see below
The Macro came back and asked me to select a cell that contains a XLOOKUP….. could eth macro be altered to allow for this?
Allister
=IFERROR(IF(NOT(ISBLANK($C16)),ROUND(IF($G16=”Salary”,XLOOKUP($C16,t_Data[Full Name],t_IMSE[Salary (per Pay)],0,0)*26,$O16*J16*26),0),””),0)
Jon,
Outstanding code! I took your additional comments to Jorge and now have this as my current code. However, your code loops through the whole workbook. I need it to make this change ONLY on the active worksheet. How would that be done?
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
‘ Disables “Enable Background Refresh” checkbox
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
‘ Disables “Refresh this Connection on Refresh All” checkbox
.Connections(lCnt).RefreshWithRefreshAll = False
Next lCnt
End With
End Sub
Hello,
Very good tips. Save me time.
Is it possible to reference one table to another table and the 2’nd table will automatically update the rows when the 1’st table updates with new data.
Thanks, Jon. I am trying using VBA to manage the order query refresh with the Querytable_AfterRefresh event. I found if I set the BackgroundQuery=False, the event will not be triggered. unless it is set to True. I am also trying to get the individual refresh state, but haven’t found a solution. Please could I have your help? Thanks! Here is the my post. https://stackoverflow.com/questions/71536996/how-to-get-the-refresh-status-of-each-power-query-of-a-excel-workbook-via-vba
Hi Jon,
Thanks for the post, this was exactly what I was looking for.
Thanks and Regards
Yecix