Read and write range by excel app scope will change the cell format

[maxzz (Max Well)]

Hi,
Like this picture

The init value is ‘10/01/2022’,it means 2022/01/10(yyyy/mm/dd),the cell’s format is General.
After read range and write range ,the value auto change to ‘2022/10/01’,the cell’s format is Date ,the date format is same with my windows(yyyy/mm/dd),the value is wrong.
And the propety ‘PreserveFormat’ is not useful.
Actually,I found use workbook will be ok,but the excel application scope how to ?

[Robinnavinraj_S]

Hi @maxzz

I also faced same kind of issue

this issue occur when we are use both workbook and excel scope in same project

if want to use excel application scope and workbook
again read the excel with excel read range activity and process that file

Thanks,
Robin

[Angel_Llull (Angel95)]

Hello @maxzz,

If I understood correctly, are you getting the two dates in different format?

After writing data table use again read data table. Then For each row in var_DataTable. Inside this use:

1. Assign Activity → var_Value = row(“Value”).ToString
2. Assign Activity → var_Counter = var_DataTable.Rows.Count.tostring
3. Write cell activity → Cell: B + (var_Counter + 1) / Text in cell = Convert.ToDateTime(var_Value.ToString(new system.Globalization.CultureInfo(“es-ES”))).ToString.Substring(0,10)

PD: Where it puts “es-ES” you need to put your country.

I hope it helps!

[maxzz (Max Well)]

Maybe my situation is different, I always only use the excel application scope, and I don’t use the workbook in the whole project

[maxzz (Max Well)]

I presumably know that it is possible to loop through the entire data table and then reassign + format control, and then this problem can be solved. But since my data volume is very large, it takes a lot of time to traverse, is there any other solution

[Angel_Llull (Angel95)]

Maybe you could try with a Macro in VBA to change the format… Would it be possible for you?