7

Enable or Disable Background Refresh on All Power Query Connections

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/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

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.

Enable or Disable Background Refresh on Power Query Connections

Click to Enlarge Image

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.

Related Articles

Fill Down Table Formulas after Power Query Refresh

Overview of Power Query for Excel

The Complete Guide to Installing Power Query

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 7 comments
Kanhaiya Shaha - July 22, 2018

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

Reply
Patrice - March 27, 2018

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

Reply
    Jon Acampora - August 2, 2018

    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! 🙂

    Reply
Eric - February 27, 2017

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

Reply
    Jon Acampora - March 6, 2017

    Hi Eric,
    You can refresh the PowerPivot connections only with the following line of code.

    ActiveWorkbook.Model.Refresh

    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.Refresh

    The 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.Refresh

    I hope that helps.

    Reply
Jorge Félix - June 14, 2016

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!

Reply
    Jon Acampora - June 20, 2016

    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! 🙂

    Reply

Leave a Reply: