Excel: Using a macro to save worksheet in .csv format

[gerrya]

Hi all,

One of the last things a macro in my worksheet does is save the sheet in a .csv format. I have a date column in the sheet formatted as dd/mm/yy. However when the macro does the 'File Save' the date actually comes out as mm/dd/yy in the saved file.

If I manually do a File-Save As, the date is saved correctly. The SaveAs method does not have any date properties e.g.

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Would anyone please be able to help me?

 

[xlbo]

This would be because you are saving comma seperated VALUES
A date is merely a serial number when it is a value. What we see as dates are just formatting. Hence, when saved as csv, what is actually being saved is the serial number of the date. When excel opens a csv file, it tries to determine what format to apply. If it sees a date, it'll try and format it to mm/dd/yy (American way round)

Things to try:
Convert your dates to text before saving
Convert back to dates by multiplying by 1

Remember, just because they are formatted differently, doesn't mean the underlying number has changed at all....you can still re-format to dd/mm/yy Rgds
~Geoff~

 

[gerrya]

Thanks for your help Geoff. I will give your suggestions a go.

Gerry