How to Paste DataTable values into Excel Macro file without changing any format

I am trying to paste the DataTable values into Excel Macro file using Write Range Activity. but while pasting the data the values which starts with Zero Prefix removed zero.

Below is the screenshot for more reference.

Below is the Datatable Values which I need to paste in Excel file as it is without changing anyvalues.

But while paste it’s removed Zero from Prefix

image

I am doing this activity in Excel Application Scope. Can anyone please help to fix this.

@nilesh.mahajan
have a check if adding ’ (single apostrophe) before the 0 starting number could be an option. The ’ is forcing Excel to keep the starting zeros and keeps the value as text

1 Like

Hi @nilesh.mahajan

Please try

  1. Changing the excel cell format to Text as cell formatted as Number may auto-remove preceding zeros.

  2. Use custom cell format to keep leading zeros.

Use a custom format to keep the leading zeros

Cell format is Text.

image

@nilesh.mahajan Is cell formatted as Text in both source and destination excel file?

or you can create a custom number format https://support.microsoft.com/en-us/office/create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

Please let me know if this helps :slight_smile:

My destination file is a Excel Macro file which I used to download from Office portal. So is there any activity through which I can change the Downloaded file’s cell format as Text before pasting data into that.

with the balareva component it is available

does this Component (Package) will support in my Production machine also? Or I need to install this packages on Production system’s Studio as well?

package needs to be available on each environment where the process is executed.
If Orchestratror is in use and the nuget feed to it is available for the execution machine, then upload the BalaReva component there (see section Libraries in Orchestratror)

Now I Observe that even if I change my Cell format as Text before pasting the DataTable values that cell format automatically change as General After pasting the values.

Before pasting values my cell format is

image

But After pasting DataTable values it change as General

image

Format as custom 000000000

Yes I tried this one, but while pasting Datatable value Cell Format again become as “Genaral” and paste the value without Zero Prefix.

Below is my Datable value.

image

and below is the paste value into Excel.

image

Below is my Main Input file from where I used to assign the values into DataTable.

image

Are you formatting the column on the sheet below directly with BalaReva Change Cell?:
image

just fyi as I have to log off
cells formatted in excel
image
Note for the text, i had to type the leading zeros after it was formatted.

set as custom here:
image