How to auto refresh Power Query in Excel (6 ways)

In Excel, Power Query updates when we click the refresh button. This is great if we only want up-to-date data at that point in time. However, there are many scenarios where we want Power Query to automatically refresh. So, how can we achieve that?

There are built-in options and VBA options. We will look at both in this post and discover how to auto refresh Power Query in Excel.

Table of Contents

• Auto refresh Power Query every n minutes
• Method #1: Query refresh
• Method #2: Full refresh
• Auto refresh Power Query on workbook open
• Method #1: Query refresh
• Method #2: Full refresh
• Auto refresh power query when cell values change
• Method #1: Query refresh
• Method #2: Full refresh
• Conclusion

Auto refresh Power Query every n minutes

Often, we want data in Excel to be as up-to-date as possible.

Unfortunately, the source data used for Power Query is not part of Excel’s calculation chain. Therefore, Excel has no idea if the source data changed or not.

A common option is to trigger a refresh at regular time intervals. Let’s see how we can achieve this.

Method #1: Query refresh

From the ribbon select Data > Queries & Connections

Then, Right-click on the query and select Properties… from the menu.

In the Query Properties window, check the Refresh Every n Minutes option and enter a time interval. The example below shows every 30 minutes.

If we want to prevent Excel from locking up when Power Query refreshes, ensure the enable background refresh option is checked.

Click OK to close the Query Properties dialog box.

The query will now automatically refresh at the set interval.

We set this on a query-by-query basis. So, we can decide exactly which queries to refresh and when. However, be aware that when a query refreshes, it automatically updates any preceding queries in the refresh chain.

Method #2: Full refresh

The method above refreshes the query, but is not a complete refresh. This is an important distinction.

If a query loads directly to a Pivot Cache any dependent PivotTables also update. However, if the query loads to a Table, and the Table is used in a PivotTable, then the PivotTable will not refresh.

For this we need a VBA macro.

Let’s start by ensuring the PivotTables refresh after the queries. In the Query Properties dialog box, disable the background refresh for each query.

Next, add the following code to the Workbook module of the workbook.

Public Time As Double
Public MacroName As String


Private Sub Workbook_Open()

'Start the refresh process when the workbook opens
Run "ThisWorkbook.RunRefreshAll"

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Stop the refresh process when the workbook closes
Application.OnTime _
    EarliestTime:=Time, _
    Procedure:=MacroName, _
    Schedule:=False

End Sub


Sub RunRefreshAll()

'Apply refresh all
ThisWorkbook.RefreshAll

'Calculate the time of next refresh
Time = Now + TimeValue("00:15:00")

'Get name of macro to run
MacroName = "ThisWorkbook.RunRefreshAll"

'Schedule the macro to run at the specified time
Application.OnTime _
    EarliestTime:=Time, _
    Procedure:=MacroName, _
    Schedule:=True

End Sub

In this line of code set the time interval for your requirements – the format is hh:mm:sss

Time = Now + TimeValue("00:15:00")

This is currently set to refresh every 15 minutes.

Save the workbook as a macro-enabled workbook (.xlsm).

When we open the workbook, the Workbook_Open macro starts the refresh cycle. When we close the workbook, the Workbook_BeforeClose macro stops the refresh cycle.

Auto refresh Power Query on workbook open

A common scenario is to ensure the data refreshes whenever the workbook opens. Let’s look at the options for this.

Method #1: Query refresh

If we look in the Query Properties dialog box again, we only need to check the refresh data when opening the file option.

Then, click OK.

That’s it. The next time we open the workbook the query updates.

NOTE: We may need to Enable Content on the first open.

Method #2: Full refresh

The method above refreshes queries but does not perform a complete refresh. So, if we want to refresh queries and PivotTables, we need to use a bit of VBA.

First, we need to ensure we disable the background refresh for each query in the query Properties dialog.

Next, add the following code into the Workbook module in the Visual Basic Editor.

Private Sub Workbook_Open()

ThisWorkbook.RefreshAll

End Sub

Save the workbook with a .xlsm extension.

The first time the workbook opens we will need to Enable the macros. After that, each time the workbook opens, a full refresh occurs.

Auto refresh power query when cell values change

Another option, is refreshing Power Query each time cell values change. Those cells could be parameters, or Tables containing the source data.

For this, we use a VBA Macro to trigger the refresh each time the specified cells change.

Method #1: Query refresh

Enter the following VBA code into the worksheet code module where the cell changes are to be tracked. This code must be in the worksheet module else it will not work).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MonitorRange As Range

'The cells to monitor for changes
Set MonitorRange = Me.Range("B5,B6:C13,Data")

'Check if change occurs in the monitored cells
If Not Application.Intersect(MonitorRange, Target) _
           Is Nothing Then

    'Refresh query
    ThisWorkbook.Connections("Query - Data").Refresh

End If

End Sub

You will need to adapt the code to your requirements:

• Change the Monitor Range to include the list of cells in the sheet to be tracked. This can be individual cells, ranges, or Table names. For example, in the code above, we monitor cells B5, B6:C13 and a Table/Named Range called Data.
• Change the name of the query to refresh. In VBA, the name of the query must be stated as “Query – [Query Name]“.  In the example above, the query is called Data.  Therefore, the full name for VBA is Query – Data

That’s it; now the macro is ready for use. 

The Worksheet_Change event triggers every time the worksheet changes. If the change occurs in any of the specified ranges, the refresh starts.

Method #2: Full refresh

To perform a complete refresh, we use the same code above with a small change.

Change this:

    ThisWorkbook.Connections("Query - Data").Refresh

For this:

    ThisWorkbook.RefreshAll

Because we are doing a complete refresh, we need to ensure background refresh is disabled.

Conclusion

In this post, we’ve seen 3 scenarios to auto refresh Power Query.

Since the built-in query refresh options do not provide a full refresh, we also looked at the VBA methods to achieve this.

Auto refreshing Power Query is a useful technique to ensure we always work with the correct data. I hope you can put this to good use.

Related posts:

• Get to know Power Query Close & Load options
• Refresh Power Query in Excel: 4 ways + advanced options
• Power Query Parameters: 3 methods + 1 simple example

---

What next?

Discover how you can automate your work with our Excel courses and tools.