Date format problem on export to CSV

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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