Date format problem on export to CSV

Hi, I am running a macro to export an xls file to csv. The xls contains dates
formatted as dd/mm/yyyy. However, when the 'save as' code is executed the
format is lost. The csv file contains mm/dd/yyyy. Any ideas how to retain the
european format so I get dd/mm/yyyy in my csv?
I have checked my regional settings and have dd/mm/yyyy enabled.
I have seen threads for the reverse issue (csv into xls) but have idea how
to fix the issue of xls to csv.
I am using Office Excel 2003 SP1 if this makes a difference.

Any help would be appreciated.
Many Thanks, Nijk

Having a US English version, it isn't something I have a lot of experience
with, but if nothing else, you can write the file with code. A start is to
use Chip Pearson's code and modify it to work with your dates.

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


"Nijk" <[email protected]> wrote in message
news:[email protected]...
> Hi, I am running a macro to export an xls file to csv. The xls contains
dates
> formatted as dd/mm/yyyy. However, when the 'save as' code is executed the
> format is lost. The csv file contains mm/dd/yyyy. Any ideas how to retain
the
> european format so I get dd/mm/yyyy in my csv?
> I have checked my regional settings and have dd/mm/yyyy enabled.
> I have seen threads for the reverse issue (csv into xls) but have idea how
> to fix the issue of xls to csv.
> I am using Office Excel 2003 SP1 if this makes a difference.
>
> Any help would be appreciated.
> Many Thanks, Nijk

Hi Tom,
Thanks for the really quick response and suggestion. I checked out Chip's
code but actually it was not necessary to go this far.
The problem was solved by a colleague of mine (thanks to Andy M.) with some
clever lateral thinking. Since Excel VBA converts the dates to US format on
the save he swapped the date format in the source file before the VBA save.
Excel was fooled into putting the date format back into European format. Here
is the code:
========================================
' The extra line required to fool excel is...
Worksheets("Sheet1").Columns(2).NumberFormat = "dd/mm/yyyy"

' Then Save the file as csv
ActiveWorkbook.SaveAs Filename:= _
"C:\AnyDir\"Filename.csv", FileFormat:=xlCSV, CreateBackup:=False
===================================
Works like a dream and very simple...
Regards
Nijk

"Tom Ogilvy" wrote:

> Having a US English version, it isn't something I have a lot of experience
> with, but if nothing else, you can write the file with code. A start is to
> use Chip Pearson's code and modify it to work with your dates.
>
> http://www.cpearson.com/excel/imptext.htm import/export text files
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nijk" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, I am running a macro to export an xls file to csv. The xls contains
> dates
> > formatted as dd/mm/yyyy. However, when the 'save as' code is executed the
> > format is lost. The csv file contains mm/dd/yyyy. Any ideas how to retain
> the
> > european format so I get dd/mm/yyyy in my csv?
> > I have checked my regional settings and have dd/mm/yyyy enabled.
> > I have seen threads for the reverse issue (csv into xls) but have no idea how
> > to fix the issue of xls to csv.
> > I am using Office Excel 2003 SP1 if this makes a difference.
> >
> > Any help would be appreciated.
> > Many Thanks, Nijk
>
>
>

This is a very late reply. Solution is to reformat the dates as text before exporting.

TEXT(date,"dd/mm/yyyy")

This exports perfectly