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

10 comments

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

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

  • 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

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

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly