There are 21 excel files how to merge the 21 excel files into single file as 21 sheets
how to merge them
It looks like this
Advantage is the sheetname can be used as cureentsheet for source
cheers
- Use Directory.GetFiles (where all the path of excel file will be stores)
- Use For each loop
use Excel Application Scope and inside that use Read Range activity - Add another Excel Application Scope outside the first one (but still inside the For Each loop).
- use the Write Range activity
- Set the DataTable variable from the Read Range activity as the input for the Write Range activity
Hope this helps
should I use 21 copy paste activities ?
No Only one…it is in side a for each file in folder actrivity…you need to provide the folder path where the files are present
cheers
You can use this Method for each file in a folder in that use filter to get all excel files, replace the folder path with your folder path
Use read range to store the data of excel sheet into the datatable
Use another excel file which will add the new sheet with file name & write the datatable to newly added sheet using insert sheet & write datatable to excel activity
Use this workflow for your ref:
Excel_auto (2).zip (124.8 KB)
Hope this helps 
how to mention all the excel files
it should print as 21 sheets in single excel file
You can create a Array of string called ExcelFilePath
Use Assign Activity
ExcelFilePath=Directory.GetFile(“Here you have to mention your folder path where all the excel file is stored”)
For having all excel file in one file but in different sheet
Create a counter int variable (Initial value should be 1)
after using write range activity
Increase counter by 1
Like this
What the code does is it iterate through all the excel files one by one saves the data in datatable & it creates the new sheet every time in our output file. I think this what you wanted right.?
I want it in 21 different sheets
Yes it creates did you run the code.? Replace the file path with your file path where you have stored the excle. & also replace the output file path where you want to create the main excel with 21 sheets.
the sheet names are different how to mention the sheet names in read range
what about the sheet names?
the sheet names are different for every excel file
create a variable called ArraySheet where you will store the all excel file sheet name
After the excel application scope
Use another for each
in for each use read range activity to iterate every sheet
can you explain how to mention the source and destination?of sheets
can you send the sample code
Add this code this will give you sheet name also, This will work on excel having different sheet names also
Find attached the whole code for your ref:
Excel_auto (3).zip (125.8 KB)
Hope this helps
Mutiplefilestosinlgefile.zip (13.4 KB)
Anil_G
jast1631
donghai
gulsheen_kaur1