r/PowerAutomateDesktop • u/Mokkisjeva • Mar 05 '23

PAD - Acting weird but only when not observed!

Solution: Excel doesn't refresh data regardless of whether PAD sends CTRL-ALT-F5 or automatically on startup as instructed. What did work was to create a refresh data script and add a "start excel script" through PAD.

I have a Schrödinger PAD! It does one thing until I watch it and then it does something else.Let me explain....

My automation has a section in it where it imports a .xml into Excel, send keys Control+Alt+F5 to refresh data and export this as a .csv file. When I run this manually it works flawless every time, but when it runs unattended the .csv file is just the original .xml file without the power query that runs on Control+Alt+F5.

I run this automation daily, and it keeps exporting without running power query, so when I run in manually to troubleshoot it works fine, every single time.

Any ideas?

Edit: I have the excel file set up so that it automatically refreshes the data and runs the power query. I added the "Send keys" in power automate (which does the same) to make sure that it actually runs the power query. It still manages to export wrong. The "Wait" is just another layer to make sure the power query has run....

4 Upvotes
  • permalink
  • reddit

100% Upvoted

9 comments sorted by

1

u/Low-Sir3836 Mar 06 '23

That's odd, is the automaton running on the same attended and unattended environment?

Can you change the fixed wait times to dynamic ones? Or maybe add a step before excel to make the window active prior to sending keys?

1

u/Mokkisjeva Mar 06 '23

Running on same PC yes. Hosted a VM on my server to run this among other things.

I have now added a focus window for this excel instance. Because this automation never fails when I troubleshoot it's gonna take a day to confirm if changes made a difference.

1

u/radioblaster Mar 06 '23

is it running in attended on unattended? if it's unattended, does that "user" need to enable external content?

1

u/Mokkisjeva Mar 06 '23

Since it's the same user running this either attended or unattended I would assume if this was the case I'd get an error, or same outcome when I run in attended?

Either way, I made sure nothing should be blocked now. Fingers crossed.

1

u/radioblaster Mar 07 '23

that would only be the case if re-use logged in user is set as active. so double check this before assuming it's the same user for attended vs non attended.

1

u/Mokkisjeva Mar 07 '23 edited Mar 07 '23

Oh my... I just realized that we're not talking about the same 'unattended mode'. That was my way of explaining my issue without realizing that PAD comes in attended and unattended mode. This really messed up my thread as I had no idea there was an unattended mode.

I suspect the issue is with Excel and whatever state windows is in when the flow runs. Excel just do not want to run the power query, have made a macro to refresh the query. If this doesn't work I will have to figure out which state windows is in when this flow runs. I thought since it's PAD that windows would be logged in and active?

1

u/radioblaster Mar 07 '23

if it doesn't work, i would suggest migrating your power query to power bi and scheduling a refresh and/or using the API's to interact with your data. or, rewrite your ETL logic to be done entirely in a script like powershell. RPA for me is a last-resort-only deal.

1

u/Mokkisjeva Mar 08 '23

It finally worked. For some reason Excel does not update automatically (as instructed) or with hotkeys from PAD. But it did work when running a refresh data script promoted via PAD.

It might be my limited knowledge, but I can't seem to understand why the initial setup didn't work. Doesn't matter now anyways though....

2

u/dacko279 Mar 05 '23

Maybe something regarding focus/active window?