Getting problems while accessing data from Excel Sheet

[SNR]

Hi,
This is Narasimha, new to this group.
I am trying to access the data from two excel files in a single script by using two For loops. Main Excel sheet contains 6 rows and sub excel sheet contains only one row. (Here based on if condition for Main excel sheet each row in Main excel sheet should go to different sub excel sheets which contains only one row)

Code:

Main Excel Sheet(6 rows)
For Loop for Main Excel Sheet
   If Header is Yes in Main sheet
       Sub Excel sheet(1 row)
       For Loop for Sub Excel Sheet
       Next
   End If
Next

While running it is running fine for first row but Main sheet contains 6 rows thatswhy it is searching for 6 rows in sub excel sheet and throwing error messages.(I have also changed to one iteration only under Run tab).

Cany any one please help me in this.

Thanks and Regards
Narasimha

[Ankur]

See if this helps... note the position of Exit For

Code:

Main Excel Sheet(6 rows)
For Loop for Main Excel Sheet
If Header is Yes in Main sheet
Sub Excel sheet(1 row)
For Loop for Sub Excel Sheet
Next
Exit For
End If
Next

Also check out the article on excel automation model for further reference.

[SNR]

Ankur thanks for the reply.
I had already tried with Exit for concept, but did n't work fine. According to your script, if I put Exit for there it will run For loop first sub excel file only and it will not go for other sub files where Header is Yes.

You have refered AOM concept, I had tried this one also, but the main problem with this is we are unable to change any data in the excel sheets if we use this concept because if we use this concept automatically excel sheets will become part of script and will become read only. Even though I had tried this also still it is giving error messages when two for loops are there.

Thanks and Regards
Narasimha

[SNR]

Hi Ankur,

I myself found the solution. I have changed the Exit For position i.e before End If and Data Table for main is called again within Main For loop second time, then it worked fine.

Thanks and Regards
Narasimha

---

Before End If but within Second For Loop