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
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0207 Auto refresh Power Query.zip
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.
TIP:
Understanding how Power Query’s background refresh works is important for getting the right behavior for your scenario.
Check this out for more information: Make Power Query refresh before PivotTables
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 SubIn 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.
WARNING:
When a macro that changes the workbook executes, the Undo stack is cleared. Therefore, you will not be able to use the Undo feature after the macro runs.
Unfortunately, this is standard Excel behavior, and not something we can prevent.
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 SubSave 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 SubYou 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").RefreshFor this:
ThisWorkbook.RefreshAllBecause 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
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy
Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.
Thank you, very timely.
Do you know if, WITHOUT VBA, we can go below the minute? Like, every 10 seconds, or even better when a data is changed?
Even if we unpack the XML code and change the setting to anything other than an integer, the setting disappears. So my guess is that 1 minute is the smallest unit.
Interesting article. Would there also a way to do a full refresh (without opening the file) without VBA code? In a couple of weeks we are switching systems and the company wants to get rid of VBA codes/documents so I am looking to keep our files working without VBA.
Not at the moment.
If Power Query comes to Excel Online, then Yes. But I don’t know if/when that that will happen.
It may be possible to do something with a Power BI data set or a Data flow. But… that could be a rebuild of everything. So, might not be a good option either.