Unanswered

For Each Iteration Limit?

(0) Share

Report

Posted on by bpearce

I am trying to process hundreds of Excel files via Power Automate Desktop (PAD). The main loop is controlled by a For Each loop. For some reason the looping fails around iteration number 100. I read about default limitations and also see info about maximum values for many of these limitations - though nothing specific about For Each loops. I also don't see any "settings" for For Each. Any suggestions?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (0)

Sort by

AshleyPettit 40 on at

Like (1)

Report
Copy link

Link copied!

Hey Mate, I had a similar issue.

Here's a few tips.

1. You can use "Loop" rather than for each. You can say start from 1-100 and increment by 1.
2. Track the completion of tasks using an X or similar. That way your program can restart where it failed.
3. You can restart the program if it crashes. Use "On block error". The failed task should throw an error. The error is caught by this. Use "Continue Flow run" -> Go to beginning on block.
4. Use an error handle. If an error occurs call a subflow like ErrorHandle and write the error to a log. Use "Get Last Error" and write this to a .txt file to help troubleshooting why the error happened.
5. Avoid using "wait until page loads" unless you have a timeout set. These will often stall on long flows.
6. If a specific row keeps erroring you should mark the row as errored and go to the next one. How I've done this is if the same row errors more than 2 times it marks the row as a fail and is skipped.
7. You can automate the flow to constantly run. Say make it run every 2 hours automatically and keep track of where its up to.
8. One of the starting tasks should be to clear up the old tasks. E.g. Terimate Processes with chrome in the title. This avoids the PC getting cluttered.

Hope at least one of the tips helps!

Was this reply helpful? Yes No
bpearce 26 on at

Like (1)

Report
Copy link

Link copied!

@AshleyPettit - great suggestions. Inspired by your comments I did a little error trapping/logging. I learned my issue was not related to loop iteration limits, but rather something related to SendKeys. Inside my loop I perform a SendKeys {Control}({Alt}({F5})) which is an Excel shortcut for Data/RefreshAll. What I am seeing per my error log is the loop performs fine for upwards of 100 iterations, then the SendKeys no longer performs a Data/RefreshAll but instead performs a ViewComments (which has a shortcut of Alt-R-A). My loop now catches this issue and jumps to a label just above the SendKeys. The retry tends to work. However once my Flow starts to behave this way, the SendKeys tends to fail on the first attempt for each loop iteration. It tends to work on the retry. While this works, it slows down my flow's throughput significantly. Any further suggestions? I realize this is a completely different problem and probably needs to be raised in a separate question... but asking here first...

Was this reply helpful? Yes No
Riyaz_riz11 4,150 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @bpearce

Before for each u hav to create one variable
nd same variable u hav to increment same var
nd use begin end exception block for for loop
nd if any exception comes it wil go to begin of for each loop so tat can eaily solve this issue

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

Like (0)

Report
Copy link

Link copied!

I have a similar issue with {Alt}(WMV) to access macros. Sometimes it gets inside the cells. Would pressing {Esc} before each {Control}({Alt}({F5})) affect anything. I feel it may reset Excel prior to giving it the Control+Alt+F5 command and it may work better.

Was this reply helpful? Yes No
AshleyPettit 40 on at

Like (0)

Report
Copy link

Link copied!

Hey @bpearce,

I'm not sure about this however, one thought is timing. Perhaps on the 2nd run items are already loaded meaning the program can run faster and perhaps this causes different behaviour.

What timing delay are you using on your SendKeys?

If you have a delay it could be reading:

Down Control then
up control then
Down Alt Then
Up Alt etc....

Make sure your delay is 0ms. Alternatively, maybe there is a better way rather than SendKeys?

Perhaps:

Save Excel, close excel, open excel - Will this trigger the same refresh you need? OR
Run excel macro. Just google an excel macro to refresh OR
Can you rebind refresh to just a single key?

Was this reply helpful? Yes No
bpearce 26 on at

Like (0)

Report
Copy link

Link copied!

MichaelAnnis - I tried your suggestion. Added {Esc}. Perhaps it helped, but my flow does still error after many iterations. Thanks for the suggestion though...

Was this reply helpful? Yes No
bpearce 26 on at

Like (0)

Report
Copy link

Link copied!

AshleyPettit- I took a different tact than your suggestion of delay=0ms... I added delay of different lengths... no improvement. I will try your suggestion.

Better way than SendKeys? Prior to using SendKeys I was clicking UIElements. As described above it works for many iterations then fails. I tried SendKeys and believe it to be more reliable... but I'm open to suggestions.

Was this reply helpful? Yes No
yoko2020 495 on at

Like (0)

Report
Copy link

Link copied!

If you don't use high end machine with 8 core and latest processor, please adjust delay between keystroke.

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

Like (0)

Report
Copy link

Link copied!

I agree, mine are getting hung up on the {Alt}(WMV) command at random places. I will keep looking for a solution. Let me know if you find one. Thanks.

Was this reply helpful? Yes No
AshleyPettit 40 on at

Like (0)

Report
Copy link

Link copied!

If you like did you want to post how you are trying to click the UI element? Perhaps you may need to change the selector or change other wait properties.

If a UI element leads to a new page load I've found it necessary to use "Wait 1 second". "Wait for Window" was not waiting long enough for me.

So if it's a new page load

Wait (ensure it's ready for the command)
Click
Wait 1s (ensure the pane is open)
Wait for Window
Launch new chrome (attach to running instance)

Or if it's a simple button which isn't a page load

Wait (ensure the page is totally loaded; try with a 5sec wait just for testing to be 100% certain it's loaded)
Click link on web page
-> Web browser instance - %AnyVariable% [Make sure you are reliably catching the web browser]
-> Advanced (wait for page to load) [Later remove this as it can cause stalls]

UI Element -> You need to make sure your accurately specifying the UI element. If you are iterating through a process theres a chance the UI element needs to be different based on the given iteration.

Below is a UI element I'm using. Note the use of the variable %RowNum%. This often works well. It's important to understand how UI elements are captured and to be a specific as possible. Being vague often picks the wrong element on the page.

div[Id="applications"] > div[Class="card"] > div[Class="row"]:eq(%RowNum%) > div[Class="mb-4 mb-lg-0 col-md-12 col-lg-11 col-xxl-11"] > div[Class="position-relative"] > h5[Class="d-inline"]

Note also on another program I had to use logic such as

If "ContractorOffer" = Yes
- Click X selector
Else
- Click Y selector

Who knows if this will help. Hopefully you're already there!

Was this reply helpful? Yes No