Saul,

I've come in on this discussion a bit late, but I had a similar - possibly
related - problem (when importing csv data with dd/mm/yyyy dates) a while
back, and found that it's a known problem with Excel 2003.

Microsoft Knowledgebase article 911750 - "The format of the dates is
incorrect when you use a VBA macro to convert a CSV text file in Excel
2003" - discusses a hotfix that's available to fix it, and also a code
modification which you can use.

I decided to use the code modification, rather than the hotfix, since it
(the hotfix) is a bit old and there have been some security updates since
it's issue and I couldn't be bothered messing around finding out what
updates I would need to reapply.

My knowledge on VBA and associated matters is extremely limited, but
assuming you're using XL2003 I suspect that what may be happening is that
when you open your csv file with VBA the dates which still appear OK are
actually imported as text, and then when you save them to a csv they show
incorrectly. Pure conjecture, I know, but it may be worthwhile using the
code modification suggested by MS when importing the data, and then see if
the correct format is maintained when you resave it.

Another article - "Converting date formats when the date isn't a date!" -
from http://www.fontstuff.com/casebook/casebook02.htm, may throw some more
light on your problem.

Just a thought. Hope it helps. :-)

Regards,

John

----- Original Message -----
From: "brawlsadford"
Newsgroups: microsoft.public.excel.misc
Sent: Wednesday, May 16, 2007 4:41 PM
Subject: date format changes when I save to CSV via a macro


Hi Dave,

thanks for your response - I appreciate the help on this one!

Whether I open the new CSV file in Notepad or Excel, the entry reads:
9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note
the change in hour format too)

The same entry in the master CSV (in both Notepad and Excel) looks like
this:
14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom -
dd/mm/yyyy hh:mm" )

When the data is pasted across into the macro workbook, the U.K. formatting
is preserved. After saving as CSV (automatically via the macro), the
formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I
then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm

It's only in the macro-saved version that this transposition to mm/dd/yyyy
h:mm is present.

Am I missing something?

Thanks Again,

Saul


"Dave Peterson" wrote:

How did you verify that the dates changed?

Did you reopen the CSV file in Excel or in Notepad?

If you used excel, try using Notepad.

brawlsadford wrote:

I'm using a macro to extract rows of data from a large, master CSV
file -
breaking it down into chunks and re-saving it as smaller CSV files.

One of the columns in the master CSV file contains date and time data in
the
format "dd/mm/yyyy hh:mm"

The macro pastes this data into the workbook fine, but when the macro
saves
the sheet:
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv",
FileFormat:=xlCSV, CreateBackup:=False

... all the dates have been transposed into mm/dd/yyyy!

This doesn't happen when I save the sheet manually (Office button/Save
As...
CSV)

My region settings are all set to U.K. - what's going on?

Thanks, in advance, for your help,

Saul