For Each Iteration Limit?

[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?

[AshleyPettit]

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!

[bpearce]

@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... 

[Ahammad_Riyaz]

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

[MichaelAnnis]

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.

[AshleyPettit]

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?

[bpearce]

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

[bpearce]

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.

[yoko2020]

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

[MichaelAnnis]

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.

[AshleyPettit]

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!