Making VBA wait for Power Query to complete refreshing all queries has been challenging for me a couple of times. Many of my custom business solutions combine VBA with Power Query. We can use VBA code to refresh all Power Query queries or only particular ones, which comes in handy in many situations.
Some simple examples:
- Refreshing a Power Query query that loads to a table in a protected sheet. (Detailed version )
- Refreshing a query and copying the resulting table to another workbook.
- Refreshing a query and letting the user know when the process is complete.
- Opening a file with queries, refreshing all those queries and saving a copy of the file with another name.
The code to refresh a query or all queries is straightforward, and one can get it by simply using the Macro recorder while performing that action.
The VBA code to refresh all the queries (and pivot tables) is:
The VBA code to refresh one particular query is:
However, it may happen that when you integrate that code within your macro to be performed with other actions in a sequence, you realize that VBA is not waiting for the query or queries to be fully refreshed and carries on with the following instructed steps, which can cause an error or alert message to pop up or the program will use a data set that is not the updated one you wanted VBA to use.
I have dealt with this scenario in many business cases and could not find a unique technique that works for all scenarios. Depending on the type of data sources you have and the different steps involved in your macro, some techniques might work better than others.
Below are some tricks that have worked well for me to make VBA wait for Power Query and carry on with the following steps only after the query refreshing process is complete. I also include a few resources that you can explore and test which one solves this problem in your scenario.
Trick #1: Use the QueryTable.Refresh method
If you only have one query to refresh or you want to refresh a few queries in a sequence, and the table or tables are based on the results of a SQL query you can use the QueryTable.Refresh method by using the following line of VBA code to refresh each query:
Or if you prefer:
The False parameter at the end makes return control to the VBA procedure only after all data has been fetched to the worksheet. It means that VBA will wait until Power Query data is refreshed.
This tip was kindly provided by Md Ismail Hosen who shares detailed VBA code here.
Trick #2: In all your queries in the workbook, change the query properties by unchecking “Enable Background Refresh”
If you have too many queries to refresh, and you want to use RefreshAll instead of instructing VBA to refresh one query at a time, or the QueryTable.Refresh method did not work in your case, you can try changing the query properties by unchecking “Enable Background Refresh.” Here’s how:
- Open the Queries & Connections Pane (Data tab > button “Queries & Connections”)
- In the queries pane, right-click each query and click Properties
- In the “Usage” tab, uncheck “Enable background refresh.”
Then, test your VBA code and see if the problem was solved.
In many cases, this action is enough to solve the issue. I recommend you read this blog post where I talk more about the benefits of disabling the “Enable background refresh” property that is always checked on by default when we create a new query.
If you have a lot of queries and do not want to change the properties of each query manually, one by one, you can run a macro to take care of them all at once. The code is available in this post by Jon Acampora.
Sometimes, however, trick #1 is not enough. I have had that happening to me a couple of times and was unable until now to identify what makes VBA still run when the “Enable background refresh” property is disabled.
I tried several methods I found online, including the Application.Wait command, but I was not happy with the results.
The trick below did work in a few scenarios, and it might work for you too.
Trick #3: Load the tables produced by the end queries to the Data Model
If the previous trick is not enough to make VBA wait for your queries to load, change the loading destination of the queries that load to your workbook to load to the Data Model as well, even if you do not need a data model in that scenario.
Here’s how:
- Open the Queries & Connections Pane (Data tab > button “Queries & Connections”)
- In the queries pane, right-click each of the queries that load into the workbook and choose Load To…
- Add a checkmark to “Add this to the Data Model.”
Test your VBA code again and see if the problem was solved. Whatever the result is, I would love to hear from you if any of these actions were helpful in your scenario.
Please note: Excel for Mac does not work with the Data Model. If you need to run the VBA code in Excel for Mac, this second trick most likely will not work.
Other resources for you to try
As suggested by Yevgeni Gaft on LinkedIn, sometimes, adding “DoEvents” after the steps related to query refresh helps solving the issue too.
Finally, I want to share Suat M. Ozgur’s proposal on Twitter:
“If there are no web queries, I have two methods to solve this problem.
1- Create query tables as class objects with events, and monitor a counter for refreshing using the AfterRefresh object event. A sample implementation is here.”
2: Using the Connection.OLEDBConnection.Refreshing property as it is shown in the following code. Unfortunately, web queries don’t change this property value unless the VBA code execution ends/stops.
- Learn how to automate your Excel reports
- Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists
- Check other posts about Power Query and Excel in this Blog,
Leave A Comment