When pasting data from datatable to Excel using ExcelApplication Scope (WriteRange), the '0' in date column is getting auto omitted

Help Activities
, ,
1

When pasting data from datatable to Excel using ExcelApplicationScope(WriteRange), the ‘0’ in date column is getting auto omitted

But if I use Write_Range_Workbook , its working fine

2

Hi

You mean value like “0001” is getting typed as “1”

In that case use a single quote before the value like this

“ ‘ “ + “0001”

This will enter the value as it is to excel and change that text format

Or

I would suggest to have a excel template already created and set that specific column to text format
So that when bot enters the value format won’t change

Hope this would help you resolve this

Cheers @Shilpa_Mohanty

3

input : dd/MM/yyyy-> 27/03/2018 or 05/02/2021
getting output in excel as → 27/3/2021 or 5/2/2021

4

There are many columns , how can i put ’ in the code

5

Can you help
@Palaniyappan

6

then in that case lets have a excel template with those columns set as text
and that would be very easy workaround

once after changing the column format to text, then use WRITE RANGE to write into that excel

Cheers @Shilpa_Mohanty

7

image
I used this , before excel application scope if I print the datatable I see the year is in correct format.
Once I put that in Write Range of Excel Application Scope, only 3 rows are getting changed(0’s are omitted) rest all rows stay intacted of the same column

8

image
last 3 rows only got changed

9

I think the input itself is of that type
Is it so
Pls check that @Shilpa_Mohanty

10

When date or month is less than 12, it is omitting the 0

11

The problem isn’t your code… it’s excel. It tends to think for us, and often makes wrong assumptions…

“Oh look, this string value ‘could’ be interpreted as a date, I’m going to change the data type wether you like it or not!”

Excel shouldn’t think… Leave it to the grownups…

It gets worse if you have different regional settings… In Dutch the decimal sign is the comma instead of the period… default date format is dd-MM-yyyy instead of MM-dd-yyyy etc…

Best to do is what @palaniyappan suggested: create a template with the specific date and number fields preformatted.

Also something to look into:
experiment with the different Write range options. Believe it or not the behaviour of these two are different when it comes to preserving formats based on regional settings:
image

image

the top left one was done with system - write range
the bottom right was done with application - write range

Using the same input DT with a row: {"0002", 3, now.AddDays(-5)}

12

The problem with only the last 3 rows changing:
Again excel thinking for you.

It interprets values as date if they comply to a format of month - day - year. This is a valid case for your last 3 rows, but not for the first set, since we dont have 17 let alone 27 months in a year.
Excel doesn’t recognise a date… it treats it as a string. The others it changes. And the method is applied per cell, not an entire column… Somebody at Microsof at some point thought this was a good thing to automate… :face_with_raised_eyebrow: