OLE2 Excel Date Auto-Conversion

aditya_govilkar — Tue, 04 Jul 2017 07:34:09 GMT

I am using OLE2 objects to pass some data into an excel file.

This includes dates specifically in the 'dd-mm-yyyy' format.

Now, excel understands if a date has been passed, and auto-converts the value into the date format. By default, this is dd-mm-yy.

So I set the NumberFormats property of the column to 'dd-mm-yyyy'.

----

CALL METHOD OF O_EXCEL
'COLUMNS' = O_COLUMN
EXPORTING
#1 = GV_COL.

SET PROPERTY OF O_COLUMN
'NUMBERFORMAT' = 'DD-MM-YYYY'. "'[$-14409]dd-mm-yyyy;@'.

----

This works, but because my excel is localized to the US, it auto-converts it to MM-DD-YYYY. But it doesn't do this consistently.

When I pass 04-07-2017 (4th July), I get 07-04-2017

But when I pass 13-12-2017 (13th Dec), I get 13-12-2017.

So when the date doesn't quite add up in the mm-dd format, excel doesn't auto-convert it. Passing '@' as the NumberFormats value got me a 5 digit number, and I cannot concatenate a ''' to pass it as text because the file needs to be uploaded elsewhere and a ''' in the date column may cause issues.

Is there a way to fix this by disabling excel auto-conversion or formatting?

topic OLE2 Excel Date Auto-Conversion in Application Development and Automation Discussions

OLE2 Excel Date Auto-Conversion