How to keep the format ( i.e. - colors ) of an Excel sheet into a newly created sheet at the same Excel file?

Hi,

To give a little bit of context to my question, I’m doing an automation with the following details:

// Pre-Conditions

  1. I have an Excel file saved on a temporary folder, that came as an attachement from an Outlook eMail
  2. That file has 5 columns with her own custom format

// Post-Conditions

  1. I need to generate a new sheet inside that Excel file with only 3 of the columns
  2. That new sheet, needs to keep the same format from the original sheet.
  3. That new sheet, however, needs to erase one row that is present on the original sheet

// What have I done so far?

So, basically… I do an #ExcelReadRange where I keep all the values into a certain datatable and then, I use that same datatable to create my new datatable with only the values I want in the new sheet.

dtCellsToRead = dtCellsToRead.Select.CopyToDataTable().DefaultView.ToTable(False, “Devise”, “Acheteur”,“Vendeur”)

&

dtCellsToRead = dtCellsToRead.Select(“Devise<>‘DZD’”).CopyToDataTable()

All of this works well with the exception of the most important detail: The format is lost!

Any idea of how to workaround this issue?

Thanks in advance.

Copy the sheet which you received over email to the main working file . Copy sheet would preserve your formating and would get as is in the source document and once the sheet is in . Remove the cells which you do not need … that’s one way of getting it done

Sadly, I’m afraid I can’t use the Copy Sheet activity. Due to compability issues, I can only use the following set of Excel activities:

Any other idea?

@moreirasa1 Is it possible to share some dummy excel file format.

Hello @indra, sure. Please take a look:

dummyfile.xlsx (10.7 KB)

Check this link BalaReva.Excel.Activities shows a custom activity for copy sheet install this custom activity works perfectly for me

Edit :
Which version of excel activies you are on ? In 2.5.2 I am able to see copy sheet , if on a lower version check if you are able to update it .

Else check if you are able to install below activity and use it

1 Like

@moreirasa1 Check attached zip file but before running install BalaReva.Excel.Activities custom activity.

ExcelCopyCustomFormat.zip (19.0 KB)

1 Like