Question

AA 360 Loop not ending

C

Hi Team,

 

I loop through an Excel file (.xmsx) containing a dynamic range of rows.

Iteration: Excel Adanced: for each row in a worksheet

 

The data is stored in a record type variable

 

But I figured out that the loop is not ending when the last row with data was processed.

 

Can someone give me a hand for that?

 

If ypu need more info please don't hesitate.

 

Tahnks in advance

 

Chris

5 replies

Tamil Arasu10
Rank
Userlevel 6
Badge +15

Hi @Christian Lichter​ ,

 

I guess that looping through empty rows that's why the loop is not ending.

 

Could you please add the if condition to skip the empty rows?

_Tamil_
F

Hi Chris,

 

I believe you can also set a counter variable to count through the data and additionally I believe there is an option that allows you to either select (Total row wth data) or (All Rows with data) - you should be checking rows with data.

 

Let me know how it works out, I can suggest a few more ideas.

 

Thanks!

C

Hi Tamil,

 

thank you very much for your reply.

Checking the conditions for he loop I was not able to find a setting that skips empty rows.

 

I found following thread:

 

https://apeople.automationanywhere.com/s/question/0D52t000003a43hCAA/excel-row-loop-executing-one-extra-time-even-though-that-row-is-blank-in-the-file?language=en_US/p>

 

I deleted manually all rows with key stroke Ctrl+- and then it works. So not sure why AA was picking up already empty cells.

 

Thanks again

 

Chris

C

Hi Fred,

 

thank you very much for your answer.

 

I had created a check to get the multiple cells in the Excel advanced package, stored in a table variable.

 

get mutiple cells 

Assigning then the row value of the data table to a number variable to get the row count.

row_count 

Converting the number variable into a string variable:

 

Number to stringAnd finally the message box that shows the result.

 

The data in the rows are consecutive, no empty rows in between.

 

The message box gives back precisely the number of rows containing data. The loop itself is looping through empty rows.

 

It is stopping that behaviour when I delete empty rows with Ctrl+- keystroke.

 

But perhaps you have a more elegant solution to get this problem tackled?

 

Thanks again for your time.

 

Cheers

 

Chris

 

 

 

F

Hi Chris,

 

Thanks for sharing the information, you know this reminded, I believe few months ago I had a use case with same scenario. You may want to try the following,

 

Put your counter beginning of the Loop action and at the end add

  • Remove Empty Cells command,

I believe once your bot iterates through the excel data, once it hits an empty cell, it will go to Remove Empty Cells and only read the cells that have data.

 

Or with the Excel Advanced command select - Create a range of start and end variables of when your data starts and ends. This way the bot should not pass the end variable or your last row/cell identified.

 

Try it out, if that does not solve the issues, try this method:

 

How to retrieve values in Excel through Loop| Automation Anywhere 2019| Excel|Loop Command - Bing video

 

Hope these help!

Reply


Terms & ConditionsCookie settings