Worksheets as collections

harshit.rawat

New Member
Hi all,
I wish to extract multiple worksheets in a workbook into collections.Though it's very easy to do it using Get worksheet as collection, but that would work when we know in advance the number of sheets present in a workbook.
How to extract when we don't know the number of sheets in workbook?

Thanks
 
Last edited:

sivagelli

Well-Known Member
You have to get the worksheet names in a workbook and then you can use the solution you have in your post repeatedly using loop stage.
Refer to the post, and the posts after on the same thread.
 

amador

Member
Not necessarily the worksheet name but worksheet count - and that derived count, use it to loop. I think you need to use the code stage to get the count because seems no action available in BP to get the worksheet count.
 

sivagelli

Well-Known Member
Not necessarily the worksheet name but worksheet count - and that derived count, use it to loop. I think you need to use the code stage to get the count because seems no action available in BP to get the worksheet count.

For getting worksheet data as a collection, you would need Worksheet name to be passed in the below available actions-
  • Get Worksheet as collection
  • Get Worksheet as collection (Fast)
    • This is an exception where no inputs are passed, but needs a worksheet to be activated before using this action
  • Get Worksheet as collection offset
  • Get Worksheet Range as collection

So, ideally using 'Get Worksheet names' action we can get the worksheet names in a workbook. This action returns worksheet names in a collection and using Loop stage with this worksheet collection as input solves the problem statement. Also, as it is a collection, you can know the rows count to get the number of worksheets in a workbook incase you need without a code stage.
I am not sure if you are referring to something else that I missed.
 

Ranjeeth

New Member
Hi, i am trying to get worksheet as collection using MS Excel VBO and adding into Queue, it gives me also some empty rows into the queue.
 

Sachin_Kharmale

Active Member
Hi, i am trying to get worksheet as collection using MS Excel VBO and adding into Queue, it gives me also some empty rows into the queue.
Hi,
Before add collection to queue .
you can use "Remove empty rows" action from "Utility-Collection Manipulation" and then add the collection to the queue.

Best Regards,
Sachin
 
Top