date format changes when I save to CSV via a macro
 

(reposted from General Questions)
(note: this behaviour seems to have appeared in Excel 2007 - in Excel 2003
the macro worked fine!)

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



"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.




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

date format changes when I save to CSV via a macro
 

You have a couple active threads in that other newsgroup.

brawlsadford wrote:

(reposted from General Questions)
(note: this behaviour seems to have appeared in Excel 2007 - in Excel 2003
the macro worked fine!)

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

"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.


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