Best way to loop through Excel

[Kladkul]

What is the best way to loop through an excel document if you don't know where the end could be?

Previously, I was using a counter that added the records up as they were put into the excel document but when I am reading them back in, the user could've added records.

 

[D.Cook]

What I've done in the past is have a requirement that the first column must always contain data. But this could be a problem if you are expecting blank rows in between data.
The most important thing is to define the exact format and rules for the spreadsheet. After that you just write the code to read that format.


Oh or another idea, you could simulate the CTRL-END key combination, which focuses the bottom-right cell of the data.

 

[Kladkul]

Thank you for your help D.Cook. Actually, this post is a bit old and I was able to find a solution.

Since I have to do some error checking/validation on the document before I process it, I loop through it once while I increment a counter. When I come across a blank field, I do a check on the other fields for that record:

IF sheet:cells:ITEM(row,2):VALUE = ? AND 
    sheet:cells:ITEM(row,3):VALUE = ? AND
    sheet:cells:ITEM(row,4):VALUE = ? THEN DO: 
          LEAVE.
END.

If more then just that field are blank I have reached the end. The other fields I check are fields I know wouldn't be blank.