Merge multiple excel file sheets into single excel file with multiple sheets

[hacky (NotHacker)]

Hi Team,

I am working on the excel automation.

In my project there are 10 xlsx files which contains single sheet each. Sheet names are unique for each excel.

My target is to have a single excel file which is a merge of all the input excel file sheets.

Input:

• xyzFolderPath\ExcelFileA.xlsx (only one sheet. SheetName = “Sheet A”)
• xyzFolderPath\ExcelFileB.xlsx (only one sheet. SheetName = “Sheet B”)
• xyzFolderPath\ExcelFileC.xlsx (only one sheet. SheetName = “Sheet C”)
• xyzFolderPath\ExcelFileD.xlsx (only one sheet. SheetName = “Sheet D”)
• xyzFolderPath\ExcelFileE.xlsx (only one sheet. SheetName = “Sheet E”)
• xyzFolderPath\ExcelFileF.xlsx (only one sheet. SheetName = “Sheet F”)
  ETC

Output:

• xyzFolderPath\Result.xlsx (All sheets. SheetNames = “Sheet A”, “Sheet B”, “Sheet C”, “Sheet D”, ETC)

HOW CAN I DO IT?

[hacky (NotHacker)]

EXPERTS : @Anil_G @Yoichi @sharazkm32

PLEASE HELP

[Yoichi]

Hi,

How about using Classic CopySheet activity as the following?

Sample
Sample20241129-2.zip (23.0 KB)

Regards,

[hacky (NotHacker)]

@Yoichi

IT Is appending the sheet to the original files instead of final file.

[Yoichi]

Can you elaborate? If Sheet1 is unnecessary, please remove it after merging.

[hacky (NotHacker)]

@Yoichi

[hacky (NotHacker)]

@Yoichi

It is simply copying and pasting the sheet on the same files (input files and one output file)

[Yoichi]

Hi,

Sorry but i don’t get your point. Doesn’t result.xlsx exist in the project folder after we run the above sample? And it has all the sheet from multiple files, doesn’t it?

Regards,

[jai_kumar2 (jai kumar)]

Hi @hacky, you want to merge all sheets into one Excel file while keeping the same sheet names, but you’re not recommending including all the data from each sheet in the new file, correct?

[hacky (NotHacker)]

@Yoichi

I am trying to use bit different approach

• Excel Process Scope
  ----- Use Excel File (Result.xlsx)

• For each file in folder (looping through all xlsx files)
  ----- Use Excel File (CurrentFile)
  ---------- Use Each Excel Sheet
  --------------- Copy Sheet (CurrentSheet.Name) (file = Result.xlsx, Sheet = CurrentSheet.Name)

Conclusion:

• UiPath is simply creating extra empty sheet in the Result.xlsx as Sheet2

[hacky (NotHacker)]

@jai_kumar2

Yes

there is 1 sheet in each input excel file.

All I want is to copy the sheet for each input excel file and move it to FInal excel file (Result.xlsx)

[sharazkm32 (Sharaz K M)]

Below code will work as expected. Give a try

Keep all the files from which you need to copy sheets in a single folder

Then follow below code. In the properties of copy sheet- provide the destination file path and keep sheet name same as the source sheet name as those are unique

@hacky

[hacky (NotHacker)]

@sharazkm32 This worked smoothly. Thanks alot Mate!