How to get data of different worksheet into different colletion

[SankalpKabra]

Hi all ,

I am unable to loop through in such a way that i can store multiple sheet data into multiple collections .

------------------------------
RPA DEV ASSTNT MGR
AIG
------------------------------

[GopalBhaire]

You can add a field of type collection inside one collection and then set that collection in loop.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

[RitaReis_Cabrit]

Hi Gopal! I'm having the same doubt... Could you please detail a bit more of your solution please? Thanks a lot!

------------------------------
Rita Reis Cabrita
------------------------------

[BenLyons]

Hi Sankalp,

I can't think of a simple way for this to be done, but there are a few options available. In order to better advise, please can you let me know if the data from one worksheet to the next is in the same format (same column number and names) and could be merged.

Also, is the workbook consistent? Does it always have the same worksheets, with the same names. Or can it have a different number of worksheets each time, with different worksheet names?

Thanks

------------------------------
Ben Lyons
Product Consultant
Blue Prism
UK
------------------------------

[AndreyKudinov]

Create Workbook collection with a field of collection type, name it Worksheet.
Open Workbook, read all worksheet names, loop over it and in that loop add row to Workbook collection and read from [Workbook Names.Name] sheet to Workbook.Worksheet collection - you'll end up with all your sheets in Workbook collection, each sheet in it's own row as nested collection.
Finally close the file&excel instance.

This can be improved by adding Sheet names etc. 

p.s. Attached screenshot and my action that you should be able to select all and copy to clipboard and just right click paste into blueprism process action bar (dont try importing it or anything). Hopefully excel vbo I picked is the right one, we got several.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------