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

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.

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

Overview of Power Query for Excel

The Complete Guide to Installing Power Query

20 comments

Your email address will not be published. Required fields are marked *

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

      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.

  • 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,

    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

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter