Losing Date formating when creating CSV File using VBA

[Nick_London]

Hi,
I'm a little perplexed by problem I am having. When I manually copy column of dates from excel, insert into a new workbook and then save the workbook as a CSV file and later open the saved CSV file in Excel, the formatting of the dates are preserved as per orginals. This is fine.

However when I doing exactly the same using VBA (code captured via the macro recorder whilst doing the above), the formatting of the dates in the CSV File change.

The macro below captures the steps in the process which returns different data fomat compared to executing manually:

[VBA]Sub Macro5()
Range("I4:I26").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("F6").Select
ActiveWorkbook.SaveAs Filename:="C:\test.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWindow.Close
End Sub[/VBA]

In terms of output, differences are below. First column is how the dates appear in the CSV File, the second column is how they appear in the orginal excel file. I would like to keep the orginal format of the dates in the New CSV File when opened in Excel. Manually copying and saving as CSV achieves this but when I use the macro, it does not. Does anyone know why this is and how to overcome?

Thanks,

Nick

New dates Orginal dates
9/14/2010 14/09/2010
9/13/2010 13/09/2010
09/10/2010 10/09/2010
09/09/2010 09/09/2010
09/08/2010 08/09/2010
09/07/2010 07/09/2010
09/03/2010 03/09/2010
09/02/2010 02/09/2010
8/31/2010 31/08/2010
8/27/2010 27/08/2010
8/26/2010 26/08/2010
8/25/2010 25/08/2010

[Bob Phillips]

This is because VBA is dumb. Whilst Excel is smart and recognises international settings, including local date formats, VBA works in US format dates, which is mm/dd/yy, so it writes it away as such.

I think your only solution is to write it out line by line, casting the date.