Refreshing Excel Power Query Queries With VBA And Power Automate For Desktop

At the end of last year two new, fairly minor, improvements were made to Excel VBA’s support for Power Query: you can now refresh and delete individual Power Query queries. These methods are available now for everyone using the Insiders build of Excel. Not particularly interesting on its own (I wrote a post a few years ago about Excel VBA and Power Query if you’re interested) but it got me thinking about this subject again. Also, at the end of last year, I upgraded to Windows 11 which has Power Automate for desktop built in. Power Automate desktop makes it super easy to automate everyday tasks and it has great support for Excel – including the ability to run Excel VBA macros. So I wondered: can you use Power Automate for desktop to automatically refresh your Excel Power Query queries? Yes, you can!

Here’s a simple example. I created a Power Query query called GetTheDateAndTime that returns the current date and time in a table with one row and column. Here’s the M code for the query:

#table(type table [RunDate=datetime], {{DateTime.FixedLocalNow()}})

Next, I created a VBA macro called RunPQQuery to refresh this Power Query query using the new refresh method I mentioned earlier:

Sub RunPQQuery()
End Sub

I then saved the Excel workbook as a .xlsm file.

Next I opened Power Automate for desktop and created a new desktop flow following the instructions here to open Excel, run the macro and close Excel again. I realised that if I closed Excel immediately after running the macro it would close Excel before the Power Query query had finished, so I added a delay of ten seconds after running the macro to give it time to finish. There are probably more sophisticated ways to solve this problem: for example you could read the value of a cell in the table returned by the query that you knew would change, then after running the query loop until the value you’ve read has changed. Here’s my desktop flow:

Finally I created a cloud flow to run this desktop flow:

And that’s it! A very simple example but very easy to implement.

5 responses

  1. Thanks Chris! Good to know as I used that VBA/PQ/PowerAutomateDesktop combination in the past. Even with SAP BEX Analyzer and AzureKeyVault involved sometimes :). Question – is your problem with Excel closing before PQ finish still exist with background refresh disabled? Don`t remember at which step I had similar problem but I resolved that by putting vba msgbox after PQ run and let PowerAutomate to wait until this will pop up.

  2. Great post..

    Since running a vba macro is not possible in cloud flow, is there any alternative to refreshing a power pivot excel saved on sharepoint or blob store using office script for example?

  3. Hi Chris, for what it’s worth, I don’t use the Refresh method of the Power Queries collection. Based on my experience, this method runs in an Async manner, unlike the rest of of VBA, which runs Synchronous. The end effect is that the VBA routines continues to execute commands while the Power Query is refreshing, rather than wait for it to complete. While you’ve solved it by effectively adding a “wait”, that’s a fragile workaround if your data set is growing. It’s a better idea to refresh the Excel table, or run a Refresh All if you are using Data Model tables to avoid this challenge.

  4. Why not just use the Workbook Open Event to Refresh the Queries by setting background refresh to False and then save close the workbook.

    You can then use Windows Task scheduler to open the file on schedule.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: