Looping through many excel sheets and ending up with one final new excel

Help
1

Hi,

I have a query i need to loop through many excel sheets and ending up with final(new) excel.

Please check the attached sheet and let me know how each cities data can be put in to one excel and also everyday each should run in a loop.

if today am going to add the 4 cities(as per attached excel) in a new excel and next day data should append to final excel.

thanks for understanding
demo_sheet.xlsx (8.9 KB)

1 Like
2

Fine
–hope these steps could help you resolve this
–use a excel application scope and pass the file path as input
–this scope activity has an output variable of type workbook and get the variable named out_workbook
–inside the scope use a assign activity like this
out_sheetnames = out_workbook.GetSheets
where out_sheenames is a varaible of type ienumerable
–now use a for each loop and pass the above variable out_sheetnames as input
–inside the loop use a if condition like this
NOT item.tostring.equals(“yourfinalsheetname”)
if true it will go to THEN part where we can these sequence of activities
like
-use a read range activity and mention the sheet name as item
and get the output with a variable of type datatable named dt
–mean while before to this for each loop create a datatable with build datatble activity with the columns we need and get the output from this activity with a variable of type datatable named Finaldt
–now coming back inside to for each loop next to the read range activity use a MERGE DATATABLE ACTIVITY where in the source mention as dt and in the destination mention as Finaldt

or
if false then goes to ELSE part where we can use write range actvity with the lastsheetname as input and the datatable input as Finaldt and enable add headers property in the property panel

so this will write the data to the final sheet irrespective of the number of sheets on each day

simple isn’t it
Cheers @Karthik_Kulkarni

2 Likes
3

@Karthik_Kulkarni
use get sheets activity image

store all the sheet names in a list, for each sheet object read and do append range to the final sheet.

1 Like
4

Kindly let know for any queries or clarification
Cheers @Karthik_Kulkarni

1 Like