Excel - getting all sheets

Malligai

Member
How can get the all sheets data into collection (For Ex : i cant predict how much sheets am getting in single excel file, it may vary depends upon the day).

(In the VBO action, we have to mention the worksheet name & i dont want that). First i have to get all the sheetname in that excel. and need to get as a collection. can anyone help?
 

VJR

Well-Known Member
The 'Get Worksheet Names' action returns the names of all the sheets in the workbook.
 

Malligai

Member
Thanks VJR :) But i want to get all the values from all sheets & save it into collection. what should i do now? (not only the names).

I have tried to get all the sheet into collection & i have added into one. (Ex first 1 & 2 , and then 3 & so on..,) But it shows some clumsy data even though all the columns are same in all the sheets. I have excluded the header. is there anything i have missed.
 
Last edited:

VJR

Well-Known Member
Hi Malligai,

First i have to get all the sheetname in that excel. and need to get as a collection
The 'Get Worksheet Names' action I suggested was for the question you asked as above and I assumed you wanted to take it from there.
Without a visual representation it is difficult to say what clumsy data you are seeing, how is your diagram and what parameters you have given to understand what is missed.

You can loop through the collection of sheet names that is returned by 'Get Worksheet Names' action.
Inside the loop pass each of the sheet names to the 'Get Worksheet as collection'. This will return an Output collection. After you use this action you need to copy rows/append from Output collection to another Final collection since the original Output collection will be overwritten each time when you use 'Get Worksheet as collection'.
 

tiwa5780

New Member
Hi Malligai,

First i have to get all the sheetname in that excel. and need to get as a collection
The 'Get Worksheet Names' action I suggested was for the question you asked as above and I assumed you wanted to take it from there.
Without a visual representation it is difficult to say what clumsy data you are seeing, how is your diagram and what parameters you have given to understand what is missed.

You can loop through the collection of sheet names that is returned by 'Get Worksheet Names' action.
Inside the loop pass each of the sheet names to the 'Get Worksheet as collection'. This will return an Output collection. After you use this action you need to copy rows/append from Output collection to another Final collection since the original Output collection will be overwritten each time when you use 'Get Worksheet as collection'.
Hi,

Will the 'Get Worksheet Names' action return the output as a collection?
 
Top