Hi Alan,
Try selecting entire column prior to formatting
oWorkSheet.Columns(5).Select
oXl.Selection.NumberFormat='dd/mm/yy'
>Hi,
>
>I am having problems outputting certain dates from a cursor to an Excel spreadsheet using OLE automation. The date format setting is SET DATE ITALIAN (ie. dd-mm-yyyy). When the day is greater than 12, the date is correctly output (eg. 24-06-2008) but when the day is less than or equal to 12, the date is output is in mm/dd/yyyy format, ie. 7th January 2005 appears as 01/07/2005 instead of the expected 07-01-2005 (with / instead of - and the days/months reversed). All dates display correctly when I browse the cursor.
>
>The automation code that loads the cell is as follows...
> oWorkSheet.Cells(lnRow,5).value = MyDateField
>
>I have tried the following, all without success...
> SET DATE BRITISH
> SET DATE DMY
> oWorkSheet.Columns(5).NumberFormat = 'dd/mm/yy'
> oWorkSheet.Cells(lnRow,5).value = DTOC(MyDateField)
> oWorkSheet.Cells(lnRow,5).value = TRANSFORM(MyDateField,'@D')
>
>Can anyone tell me how to get all dates into the correct format? Why is Excel trying to convert dates to American format? Any help would be appreciated.
>
>TIA
>Alan