Using Power Automate Desktop to refresh Excel file with web query. How do I force the flow wait for Excel to finish the Refresh All?

(0) Share

Report

Posted on by SpencerM3

Hello,

I've been working to automate the process of refreshing a number of Excel files that have built in Web Queries.

I need the flow to open the Excel file, press Ctrl-Alt-F5 to "Refresh All" then wait for the queries to complete before saving and closing the file.

My issue is that I cannot find a way to tell flow to watch the progress of the web query in Excel. I would rather not resort to hard coding a long wait time.

The status bar in Excel does report some progress messages along the way, but the number of queries in these Excel files can be variable. (So I can't code a "wait until you see this three times"). Really need Excel to tell Power Automate "I'm done with the Refresh All."

Screen shots to illustrate:

Queries & Connections Pane:

Progress during the query...

Categories:

Power Automate Desktop

I have the same question (4)

All responses (12)

Answers (1)

fraenK 2,125 on at

Like (0)

Report

You can click on the message in the status bar and in the resulting pop-up window you'll see the overall status including a status message when all queries have been completed.

Was this reply helpful? Yes No
SpencerM3 10 on at

Like (0)

Report

The status message at the bottom of Excel can't be clicked while it's running. My cursor goes to a blue waiting circle while it's running and often Excel goes to (Not Responding) while the refresh is running.

Do you know another way to force a status message to appear? Or know a process I can have PAD watch and wait for?

Was this reply helpful? Yes No
Verified answer

fraenK 2,125 on at

Like (0)

Report

Seems like you're running an older Excel version?
Of course you can run a loop looking for the text in the status bar until there is a longer timeout.

Was this reply helpful? Yes No
SpencerM3 10 on at

Like (1)

Report

Version 2008 (Office 365).
I can try the look or was going to setup a summary page that had a formula that would register a value when everything was done.

Previously I was doing this with AutoHotkey and it had the intelligence built in to wait for Excel to complete it's action. Would think a MS product would at least have the same capability.

We are both talking about the status bar at the bottom of Excel next to the horizontal scroll bar, right?

1 people found this reply helpful.

Was this reply helpful? Yes No
fraenK 2,125 on at

Like (0)

Report

Yes, status bar, bottom left corner. PAD has originally been developed by Softomotive, so it's not really a full Microsoft product just yet 😉

1 people found this reply helpful.

Was this reply helpful? Yes No
sebastiendb 69 on at

Like (1)

Report
I had the same problem. I solved it by coding VBA.
Refresh all connections through a VBA macro. My queries are Power Queries, so I can chose not to refresh them in the background. This makes sure the VBA script doesn't continue until the refresh operation is finished. You can either uncheck background refresh on the 'Power Query' query properties, or through VBA parameters that specify the refresh operation.

I believe this approach is much more robust. Please mark my comment as a solution and give me a thumb if you agree.

Two downsides of this method:
queries will refresh sequentially (it's automated, so maybe it's not needed that you sit there waiting)
when not refreshing in the background, Excel freezes until refresh is done (again, maybe you don't need that instance of Excel at that moment, because you automated it).
if there is an even better solution, I'd like to here.

Was this reply helpful? Yes No
TerrySchmidt 19 on at

Like (1)

Report

This Works for me Thanks

Was this reply helpful? Yes No
Benny_1857 124 on at

Like (1)

Report

In the excel screen left bottom, if you query is running, it show this A
Once the query completed, it show this B

You could use UI Automation - Wait for Image
Once A disappear or Once B appear
I use the 2nd way and it works stablely.

1 people found this reply helpful.

Was this reply helpful? Yes No
sebastiendb 69 on at

Like (0)

Report
@Benny_1857
what if I open another app on top of Excel (will it trigger, because it will never be visible on the screen)?
what if I resize Excel and move the application around on the screen or to another screen, will your solution still work?

Was this reply helpful? Yes No
Benny_1857 124 on at

Like (0)

Report

1. if the image is covered or hidden, it will not be triggered. But I believe the PQ refresh function will be added into excel group in future.
2. I have not tried 2 screens before, you could have a try ^_^

Was this reply helpful? Yes No