Wants to Merge Multiple Excel Files into one excel File without losing Color Code in cells

Help Activities
, , ,
1

Wants to Merge the Multiple Excel Files having same headers and different values containing colors in some cells into One Excel File without losing the Color in Output Excel file.

2

Hey when you read the excel with yellow coloured cells can you check preserver format as True and then see if it stores the coloring info for cells and merge these datatable into one using Merge datatable activity

OR If you already know where its highlighted then you can also use Set Range Color

3

@prateek.mehandiratta9 , it doesn’t works any other alternate solution…as color cells are not fixed

4

Hi @Shubham_Arora1

Possible alternate way

Regards,
Sach!n

5

Hi @Shubham_Arora1
Try following the below steps to achieve the output:

  1. Use the “Assign” activity to create a new DataTable variable to store the merged data. Let’s call it mergedDataTable. Initialize it using New DataTable().
  2. Use the “Excel Application Scope” activity to read each Excel file one by one. Place the following activities inside this scope:
    a. Use the “Read Range” activity to read the data from the Excel file into a DataTable variable, let’s call it currentDataTable.
    b. Use a “Merge DataTable” activity to merge the currentDataTable with the mergedDataTable created in step 1. Set the “Destination” property to mergedDataTable.
  3. After the Excel Application Scope, use another “Excel Application Scope” activity to write the merged DataTable into a new Excel file. Place the following activities inside this scope:
    a. Use the “Build Data Table” activity to create a DataTable variable with the same structure as the merged data. Let’s call it outputDataTable.
    b. Add columns to the outputDataTable with the same names and data types as the merged data using the “Add Data Column” activity.
    c. Use a “For Each Row” activity to iterate over each row in the mergedDataTable.
    d. Inside the “For Each Row” loop, use the “Add Data Row” activity to add each row from the mergedDataTable to the outputDataTable.
    e. Use the “Write Range” activity to write the outputDataTable to a new Excel file.
  4. Save and run the workflow.

Hope it helps!!
Regards,

6

@Parvathy , it will lose the color cells, that should not be

7

@Shubham_Arora1

Please use copy/paste range activity it has a property to copy format as well

https://docs.uipath.com/activities/docs/copy-paste-range-x

Cheers

8

@Anil_G , i think it has the property for single excel file. Please tell me if i am wrong

9

@Shubham_Arora1

If you want to do it between excels then use two use excel file activities one inside another and using excel reference as select each in source and destination

Cheers

10

@Anil_G yeah it’s giving exception

  1. excel application scope - for destination file
  2. for each files in folder
  3. excel application scope files - source file
    then copy/ paste range activity

but giving exception sheet doesn’t exists

11

@Shubham_Arora1

Can you please show how you configured it

Cheers

12

@Anil_G i am unable to post from server, if possible for you to have workflow
thanks in advance.

13

@Shubham_Arora1

This si how it looks

image
image543×646 32.2 KB

BlankProcess - Copy (3).zip (20.1 KB)

please check excel.xaml…it is copying colors as well

you can specify range if you need else it copies everything in the sheet…in destination it is enough to give the start cell

cheers

14

@Anil_G, i think not possible in Classic as me using Classic Activities, requirement too use classic activities and UiPath activities.

15

@Shubham_Arora1

Then you cannot do directly…but first use a copy sheet and copy the sheet to destination workbook then use copy range and copy the range as needed…now after copy as both are in same excel you would not have an issue

cheers

16

Hi @Shubham_Arora1

Can you try this-

  1. Use the Directory.GetFiles activity to retrieve a list of all the Excel files in the specified folder.
  2. Initialize an empty DataTable that will hold the merged data. Let’s call it mergedDataTable.
  3. Iterate through each Excel file using a For Each loop. Set the TypeArgument property to String to represent the file paths.
  4. Inside the loop, use the Excel Application Scope activity to open each Excel file.
  5. Within the Excel Application Scope, use the Read Range activity to read the data from each file. Store the output in a DataTable variable, let’s call it currentDataTable.
  6. Use the Merge DataTable activity to merge currentDataTable with mergedDataTable.
  7. After merging, use the Excel Application Scope activity again to create a new Excel file.
  8. Inside the Excel Application Scope, use the Write Range activity to write mergedDataTable to the new Excel file.
  9. To preserve the cell colors, you can use the Preserve Format option of the Write Range activity. Set the PreserveFormat property to True.
  10. Save and close the Excel file.

Thanks!!