Skip to main content
Solved

How to Loop Through Excel Blocks and Restart Loop for Each Block

  • 20 August 2024
  • 5 replies
  • 47 views

Hi Team,

I have a requirement in Automation Anywhere where I need to process blocks of data from an Excel sheet. The sheet is structured with different blocks of data, each ending with a specific keyword (e.g., "SP1", "FR1" and it may varies as well). After processing one block, I need to restart the loop to process the next block, and this continues for all blocks in the sheet.

For example:

  • Start with the first block (rows 1-6), then move to the second block (rows 8-13), and so on.
  • Each block ends when a specific keyword is found (like "SP1" or "FR1"), which indicates the end of that block.

How can I set up the loop to automatically detect the end of one block and start processing the next block in Automation Anywhere? I want to avoid hardcoding the row numbers since the number of rows in each block may vary.

Could someone please guide me on how to implement this? Detailed steps or an example would be greatly appreciated!

Thanks in advance!
 

S.NO URL
1 PREPROD
2 PREPROD_HHT
3 PREPROD_HHT1
4 PREPROD_HHT2
5 API_POST
6 API_GET
7 SP1
8 DEV
9 DEV_HHT
10 DEV_HHT1
11 DEV_HHT2
12 API_POST
13 API_GET
14 FR1
15 TEST
16 TEST_HHT
17 TEST_HHT1
18 TEST_HHT2
19 API_POST
20 API_GET
21 FR1


Thanks & Regards,
Charan K
 

Hi @Charan 3219,

I hope followings steps will help you to achieve this.

 

  1. Read the Excel Sheet:

    • Use the “Excel Advanced” package to open and read the Excel sheet.

  2. Initialize Variables:

    • Create variables to keep track of the current row ($currentRow$), the total number of rows ($totalRows$), and the keyword ($keyword$).

  3. Get Total Rows:

    • Use the “Get All Rows” action to determine the total number of rows in the sheet and store it in $totalRows$.

  4. Loop Through Rows:

    • Use a “While” loop to iterate through the rows until $currentRow$ is less than or equal to $totalRows$.

  5. Read Each Row:

    • Inside the loop, use the “Get Cell” action to read the value of the current row and check if it matches the end-of-block keyword.

  6. Check for End-of-Block Keyword:

    • Use an “If” condition to check if the cell value matches the end-of-block keyword (e.g., “SP1” or “FR1”).

  7. Process the Block:

    • If the keyword is found, process the block of data. You can use a sub-task or another loop to handle the processing logic.

  8. Move to the Next Block:

    • After processing the block, update $currentRow$ to the next row and continue the loop
 

Hi @Tamil Arasu10 

Could you please help me with some screenshots to better understand.

Thank you in advance.

Charan k


I have stuck at 8th step can you please post some screen shots to understand better @Tamil Arasu10 
 

 

Hi @Charan 3219,

Please try below 

Please note that Current row value is 1.

If the keywords matched, you can continue process else skip move on

In the end, please close the excel.

 

 

What are the parameter that you are passing in the “Get Single Cell”.
And also if condition find the work SP1 the i will go the loop and start the second block right.

Thank you @Tamil Arasu10 

Hi @Charan 3219,

Please try below 

Please note that Current row value is 1.

If the keywords matched, you can continue process else skip move on

In the end, please close the excel.

 

 

 

 

Reply


Terms & ConditionsCookie settings