cancel
Showing results for 
Search instead for 
Did you mean: 

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

kathireshy
Level 3
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?
------------------------------ Thanks & Regards, Kathiresh Yoganathan Application Developer IBM ------------------------------
1 BEST ANSWER

Helpful Answers

kathireshy
Level 3
I figured out the issue, There is a formula in 4th row 10th column which is not in a proper format.
------------------------------ Thanks & Regards, Kathiresh Yoganathan Application Developer IBM ------------------------------

View answer in original post

5 REPLIES 5

PvD_SE
Level 12
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
Happy coding!
Paul, Sweden
(By all means, do not mark this as the best answer!)

kathireshy
Level 3
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.
------------------------------ Thanks & Regards, Kathiresh Yoganathan Application Developer IBM ------------------------------

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
Level 3
I figured out the issue, There is a formula in 4th row 10th column which is not in a proper format.
------------------------------ Thanks & Regards, Kathiresh Yoganathan Application Developer IBM ------------------------------

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.