cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SA_VOH
Helper II
Helper II

Regarding Power Query Refresh on Multiple Excel Worksheets using PAD

Dear Community,

Using PAD, i have made a flow wherein :-

1. I open a Excel workbook.

2. Using a Recorder, i went to Data Tab, and then Refresh and the Refresh All.

3. My Power Query Runs successfully and all the Queries and connected Pivot Tables are refreshed at one go.

 

Everything is fine upto here. No issues at all.

 

However, the moment i copied-pasted the above flow so that it can work on my 2nd workbook, there seems to be some issue/error in PAD. 

Image 1.PNG

 

I think this error is coming is because the flow captured in steps 1 to 3 was specific to the 1st Workbook and doesn't run for 2nd workbook and the screen is enclosed when i opened the error point in detail.

Screenshot 2022-06-28 170251.png

 

Are flows prepared using UI Recorder in an Excel file, becomes specific to that Excel File only and doesn't run on other Excel Files.

Any help would be of great use.

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions

Perhaps this picture makes it a bit more understandable.

You can replace the text 'Book1 - Excel' with a variable or set for example 'Ends with' "- Excel".

 

PAD-Customize UI elements.jpg

View solution in original post

4 REPLIES 4
Gidi
Resolver I
Resolver I

Hi,

you can customize the UI selector in the repository (see at the right pane below variables). You can change it to a more flexible name (e.g. by using a variable) or if you expect to have only one Excel sheet as foreground window at flow run, remove the workbook name and set it to contains “- Excel“ instead. 

SA_VOH
Helper II
Helper II

Hi Gidi,

Sorry to worry you, but i couldn't understood your reply. Could you please elaborate diagrammatically, if possible.

Regards,

Perhaps this picture makes it a bit more understandable.

You can replace the text 'Book1 - Excel' with a variable or set for example 'Ends with' "- Excel".

 

PAD-Customize UI elements.jpg

SA_VOH
Helper II
Helper II

Hi Gidi,

Thanks for the solution. It worked, although i created over 55 steps in PAD for my 7 Excel files. Just copied and pasted the Recorder for each of my file.

Was thinking whether i could use the "Gel files from Folder" Option.

However, please let me know whther there is an Option in PAD to get Excel files from Folder, and then run them in a Loop. 

From my observations, i have observed that putting Excel files (which are in folder), in Loop and opening them one by one is not possible.

Regards,

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Kudoed Authors
Users online (2,720)