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?

[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...

[fraenK]

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. 

[SpencerM3]

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?

[fraenK · Accepted solution]

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.

[SpencerM3]

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?

[fraenK]

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

[Sebastiendb9]

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.

[TerrySchmidt]

This Works for me Thanks

[Benny_1857]

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.

[Sebastiendb9]

@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?

[Benny_1857]

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 ^_^

[Anonymous]

You could run a Macro Enabled Excel file.

If you are using Power Query then you will need to turn the "Run in Background" option off for each query so Excel does not run the code and updates asynchronously. Then set the flow to call this Macro

Private Sub UpdateWorkBook()
ThisWorkbook.RefreshAll
ThisWorkbook.Save
Application.Quit
End Sub

[BigE1]

I was able to get it to work using the same method as originally posted using the whole bottom status bar looking for the word refreshing.

Use Action: Wait for window content.

Waite until window: Doesn't contain text.

Text: Refreshing (You may not see this text but it's there)

Add UI element: Select the whole bottom status bar, not just receiving data from web site.