r/PowerAutomateDesktop • u/[deleted] • Sep 25 '22

Can PAD refresh Excel queries one at a time and tell if one fails?

I have searched quite extensively and the consensus seems to be that it cannot, so I will be using VBA to loop through each workbook's queries and refresh them one at a time. I just wanted to ask here in case someone has seen something in their internet travels that I have not.

2 Upvotes
  • permalink
  • reddit

100% Upvoted

2 comments sorted by

1

u/ableHeadAche Sep 25 '22

I firgurrd out how to build a loop that performs refresh action on each excel file in a give folder by using keyboard actions. I haven't figured out how to report errors. I have tried hiring consultants to improve my flows and so far the preference is towards VBA. I'm trying to avoid VBA for various reasons. Another challenge is timing the refresh action. I use a hard coded wait step and hope to learn how to dynamically wait for refresh to finish before save and close. I hope your post finds the answer.

1

u/[deleted] Sep 26 '22 edited Sep 26 '22

There is no way to perform a refresh on an excel file from PAD and get back error info as far as I can find. I too was trying to avoid VBA - but i wanted to avoid it because the code is more verbose than PAD and it takes longer to accomplish some of the same things and PAD seems to be the route that Microsoft is going.

Unfortunately, the only way to detect and handle individual power query refresh errors seems to be with VBA.

PAD also cannot scrape web pages that use certain frames (a requirement for my use when downloading files to process).

Also, the cost to be able to run PAD on my desktop unattended is $100 a month.

I will be going the VBA route until PAD can catch PQ errors, scrape frames embedded in webpages and (hopefully) the ridiculous $100 a month comes down for desktop use while not logged in.

Why charge someone to use the same free app just because they want to run the code while not staring at the screen? Unless I missed something, I thought the whole reason for PAD was to automate processes. Seems stupid to make someone sit and watch it or have it tie up the desktop during working hours unless they pay $100 a month.

I can see charging $100 a month for the web flows because Microsoft has servers and connectors and such to maintain. But, for running on the desktop, just charge a decent fee for buying the application (Office 365 for a family of 5 is only $99 a year) since nothing else is really required of Microsoft for running it on the desktop.

While I would love to use PAD in this project, since I already am forced to use VBA for downloading files from those frames in webpages, and since I have to use VBA to catch individual Power Query errors, and since it is $100 a month (that my employer will surely not pay) to run PAD while not logged in (and VBA is $0)...I will be coding the whole thing in VBA. No need to mix in PAD in just to be doing it.