Urgent: MS Excel VBO - Get Worksheet as Collection is not reading all rows

[kathireshy]

Hi Team,

I am trying to read excel as collection and that particular excel is having 110 rows. When I use MS Excel VBO - Get Worksheet As Collection, Get Worksheet Range As Collection and Get Worksheet As Collection(Fast), all these actions are reading only the first three rows from that worksheet and it is skipping the remaining rows.

Can someone help me on this?

[PvD_SE]

Hi K,

Sounds like there could be some bad data in your XL. If you step into the VBO action 'Get Worksheet As Collection' and run the code stage, does it present some sort of error there?

If you convert your XL to a CSV and retry the 'Get Worksheet As Collection' against the CSV, does that work any better? If not: does the CSV show any invalid values if you browse it with Notepad++? (don't use MS Notepad for this check...)

Happy coding!
Paul
Sweden

[kathireshy]

No, The excel workbook that I am reading is having four sheets and the sheet which I am trying to read is having some formulas(reading values from other sheets). I am not getting any error message while reading the excel file. It is reading only first three rows along with column names and skipping the remaining 107 rows.

[krishna.kodimurali9]

Hi Kathiresh,

What is the range you are passing into the Get Worksheet As Collection action? I believe you taking the row count and then using it to determine the end row.

[kathireshy · Accepted solution]

I figured out the issue, There is a formula in 4th row 10th column which is not in a proper format.

[krishna.kodimurali9]

Thanks for letting us know the resolution. 
When we deal with excel automaton most of the issues and solutions lies within excel. 
It's always recommended to know excel better as it will make lives easier.