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()
ActiveWorkbook.Queries("GetTheDateAndTime").Refresh
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.
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.
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?
Hey Rakesh,
Would be happy to hear more about your workflow.
Please Email me @ jokahati@microsoft.com
Thanks,
Jonathan (Excel team)
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.
https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculateuntilasyncqueriesdone
Meant to reply to this Caleb. My experience with this routine has also been mixed. I have seen issues where it just did not pause an went straight on to execute the next line of code. It has been a long time since I tested it though, so it is possible that it has been fixed…
Hi Ken, the solution Chris is using is because of limitation of Power Automate, i.e. it would close the file if no sufficient time given to refresh the data. In your comment, what is the method you are using if you want to schedule an automated refresh of an excel file that is located in SharePoint directory? I just wanted to clarify. Thanks
Hi Albertan,
For what its worth, I’m not sure Power Automate actually waits for the VBA to be done at all. In my experience with PA, it is really good at starting something, but not so much in receiving data back to know if the job was completed by the client or not. (I may be wrong here, but this is what my experience is.) This is where the need to add the 10 second delay comes in… it isn’t saying the refresh is done, it is simply providing enough time to let it be finished.
To be clear, my comment re the Async/Sync nature of queries is very much based on my experience with VBA and VB.NET. The most reliable way I’ve found to ensure the data refresh completes before the program moves on is to refresh the destination, not the query. But I don’t believe today that PA even sees that. It simply says “go”.
So as far as refreshing from SharePoint… time your refresh manually in desktop. Take that value, double it (to be safe), and use it as the timeout in the way Chris mentions.
Ken, I found the statement “CalculateUntilAsyncQueriesDone” to be reliable. I made an example spreadsheet about its proper usage in a github repository here.
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.
The VBA code below is a way to allow the system to wait until each process has finished before continuing:
Sub Wait()
Dim PauseTime, Start, Finish, TotalTime
PauseTime = 1 ‘ Set duration.
Start = Timer ‘ Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish – Start ' Calculate total time.
End Sub
This seems to work for my queries that require the previous one to finish before
I’m going crazy with the refresh method. I have a Query not linked to any table (it’s used as source for other 4 tables) and when I call myworkbook.queries(“myquery”).refresh it rise an error (classic 1004, already disabled background query).
Manaual refresh works.
If I add a table linked to this query and I refresh the table.. it works (actully done that way).. but it’s not the solution 🙁
I think you’ve misunderstood how Power Query queries work: if a query is not linked to a table (in the worksheet or in the Excel Data Model) it can’t be refreshed. In the Power Query Editor you’re refreshing the preview you see there, you’re not actually refreshing the query.
have a question that I have been searching the web for for awhile. I’m hoping you can help.
I was hoping to remove a list of about 170 customers from my query. I have their emails and that’s how I need to eliminate them. I know I can filter and copy and paste each and every email in to text does not contain but is there a way to copy and paste the whole list into the advanced editor ,not having to manually type all the and not text contains, etc around each and every email address?
Thanks in advance!
Assuming you have a second query with the 170 email addresses listed, you need to do a Merge with an anti-join https://learn.microsoft.com/en-us/power-query/merge-queries-left-anti
Hi Chris, thanks so much for the quick reply. I don’t have a second query. Should I make one? I don’t think the new list with these emails are in will be the same format as my original data. Is that ok?
Yes, you will need to have a second query to do this. Both tables will have to have columns with the email addresses in the same case so they can be matched.
I can’t thank you enough Chris I learned how to merge, did the anti join, and then it just took a few seconds and all the unwanted email are gone. Thanks so much!
This office script refreshes all data including power query when is ran from desktop excel mac but it wont refresh data when is ran from online excel. I am trying to power automate to refresh the power query when “sharepoint excel item is modified or created” trigger but the run script action doesnt refresh the data, only when is ran from desktop excel. Why? and how can I fix this?
function main(workbook: ExcelScript.Workbook) {
// Refresh all data connections
workbook.refreshAllDataConnections();
}
PQ Refresh from SharePoint isn’t supported in Excel Web yet, but it will be released soon.
Stay tuned for updates 🙂
Jonathan – Excel Team