Merging multi excel files into one sheet with colors as original sheets!

I have 14 files in folder and sheet name is “Worksheet” as below.
They all have same headers, I need to merge all of them into one sheet and I want all colors same as original sheets and header only once at the top.
Can someone tell me how to solve this?

Studio 2022.10.8 (Classic design & current UiPath.Excel.Activities is 2.5.4 ) as I can use now.

Is it possible to share one sample file to see the formatting details

Sample file is same as original as in my question.
But I want header is only once at the first time due to same headers for all files.

Hey @myakkhaing these are the steps you have to use
create a string variable which -store the folder path
create a array of string - and use directory.getfile method and pass the folderpath which holds the files location
use a for each inside for each use excel application scope and inside the excel application scope pass the currentitem which get all the files one by one and inside excel application scope use read range
PS- your sheet name is fixed so use the name and if is not fixed and you only want the first sheet and use getworkbook sheet after read range use merge datatable activity and outside the loop use right range .
so for writing use 1 build data table activity clear all the columns and row data inside the build datatable and create a variable in the build datatable and pass that output in the write range and check the write range add header option also enable the preserve format in the read range activity.

if this activity is available for you you can use it otherwise use directory method

cheers

@myakkhaing

Best is to go with copy paste range activity that would copy all the formatting as well

To know what area to copy and all you can go with excel.sheet(“sheetname”).Rowcount give the number of rows as columns are fixed you know the full range

Cheers

@myakkhaing
Plus you will probably need to copy entire sheet from source file to destination as the “Copy/Paste Range” activity only works within one file. And delete it after Copy/Paste.

Activity documentation with example
Activities - Copy Paste Range

Cheers

@J0ska

just to clear, you can specify the source and destination ranges as well

cheers

@Anil_G
I believe we are on the same page.

You must indeed specify source and destination. It is basis for copy-paste :slight_smile:
But both source and destination must be within the same excel file given the “Copy/Paste Range” activity operates in context of “Excel Application Scope”

E.g. in below screenshot it will copy range from “Sheet1” to “Sheet2” of file “Book2.xlsx”

Cheers

@J0ska

You can copy to different excels as well using modern activities

Cheers

Unfortunately I can’t use Modern activity here.
How about if I use Send Hotkey for copy/paste?

@myakkhaing

the version of studio and excel supports modern …is there some other issue to not use modern?

then better go with writing a macro and invoke it from UiPath

cheers