Answered

loop through files using PAD

(0) Share

Report

Posted on by averma

Hi Readers,

I am trying to loop though excel files in a folder to update the same content. There are 72 files in total. I can see the files are stored with file numbers in the variable 'files'. Is there a way I can invoke the file number to set up a loop? or any alternative way to loop through files using PAD would be useful please.

Thanks in advance.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

So, the first command 'Get Files in Folder' already labeled your list as Files.
When you used Set Variable %NewVar% to Files [0], you are calling NewVar the first file name.

Your ForEach command needs to be pointed at %Files%...get rid of the Set Variable command.

Was this reply helpful? Yes No
averma 43 on at

Like
a
(0)

Report
Copy link

Link copied!

@MichaelAnnis Thanks for pointing that out MichaelAnnis. Rookie mistake! 🙂 but what I am actually trying to do is to generalise the UI elements. I saw the post by @Anonymous a.k.a Loop over several Excel files and was trying to assign a new variable to capture the generic name.
I tried and currently am failing;
I tried to update the UI element name with and without '-Excel' at the end without success. I also saw your response to another query last a.k.a Perform actions on a specific workbook selected from a file. where you suggested using * at the end of the common name denominator. Just tried that and its still not working.

Any idea where I am going wrong?

Was this reply helpful? Yes No
tkuehara 667 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @averma

What exactly is failing? Please check how your title bar is displayed on screen because it can be displayed with and without extension:
In my case (with extension) I only changed the name parameter to:
I also noticed you are in the "Visual editor" mode. Please change it to "Custom":
I did a sample test to open each excel file in a folder and activate the filter option and it worked:

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@averma,

I was wrong with the "*"...I will have to fix that on the other post. My apologies. In the "Name" box, there is a selector to change from "Equal to" to "Contains", then just leave in the text field only that which is the same in all filenames.

On your other option, I noticed in the first photo above, %NewVar% was set to Current Item (the file path and name) without extension. Then %NewVar% is used in your selector "Name=". I think this is an error, because looking at my default selector below, it is only the name.extension, not the path (note: extension is included):
So, you will need the filename only, which I believe @tkuehara addressed would equal CurrentItem.name.

Best of luck.

Was this reply helpful? Yes No
averma 43 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Guys

@tkuehara Thanks for your reply. I tried both '%CurrentItem.Name% - Excel' and just %CurrentItem.Name% and it didnt work.
Apparently I tried @MichaelAnnis suggestion of altering it to Contains and use a word just to check and it worked. But I need to be able to use the generic variable for all the files.
Also do I need to change the UI element Name as well? I tried and still didn't work though! I have posted snippets in the hopes that you might be able to pick up where I am going wrong.

Kind regards
Amit

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You should not have to do it on the underlying elements. Elements are created in a tree structure, so all the excel elements (if you found them under the same original workbook) should be managed by that top selector (the window name) which we are trying to troubleshoot.

Is each file the only excel file open on the computer at any given point in time? If so, simply uncheck "Name" from the right menu bar. It is unnecessary information, and the bot will look for the only open Excel window for the selector. I got it to work here:

Best of luck.

Was this reply helpful? Yes No
averma 43 on at

Like
a
(0)

Report
Copy link

Link copied!

@MichaelAnnis It worked. Gutted though to keep hitting error with generic variable though but your solution helped me move on. Thanks

Kind regards

Was this reply helpful? Yes No