How to merge different excel files into single file as different sheets

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


Hi @anjani_priya

  1. Use Directory.GetFiles (where all the path of excel file will be stores)
  2. Use For each loop
    use Excel Application Scope and inside that use Read Range activity
  3. Add another Excel Application Scope outside the first one (but still inside the For Each loop).
  4. use the Write Range activity
  5. 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


Hi @anjani_priya

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 :slight_smile:

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 :slight_smile:

here it is

@anjani_priya (13.4 KB)