Mysterious re date Format

[michael.beckinsale]

Hi all,

The following is code snippet whereby l am copying a worksheet to make
a seperate Excel workbook and then if certain criteria are met l also
save the file as a text file.

The original .xls file has the correct date format (dd/mm/yy), the
worksheet saved as .xls has the same correct date format (dd/mm/yy)
but the saved .txt file when opened in notepad has the incorrect date
format (mm/dd/yyyy)

Regional settings are correct (English UK)

I have also tried adding Local:=True to the code.

But the really frustrating thing is that if l manually copy the sheet
and save as a txt file the date appears correctly !

Can anybody please help?

Code - beware word wrapping

If MyWBname <> "FT CC Report (2).xls" Then
ActiveWorkbook.SaveAs WBpath & MyWBname
If MyWBname Like "??? Journal.xls" Then
If MyWBname <> "EDE Journal.xls" Then
ActiveWorkbook.SaveAs WBpath & Left
(MyWBname, Len(MyWBname) - 4), FileFormat:=xlText, Local:=True,
CreateBackup:=False


End If
End If
ActiveWorkbook.Close True
Else
ActiveWorkbook.Close False
End If

 

[Peter T]

When you say "has the correct date format" is that simply the default
International date format you have in your system. If so try changing it
slightly, eg

Select your cells, Ctrl-1, custom
dd/mm/yy;@

or with code
rng.numberformat = "dd/mm/yy;@"

Regards,
Peter T