Date displayed in CSV file not the same as what is saved in the file

[exce101]

Hi All,

I have a macro that exports data from an xlxm file into a single tab and saves it as a csv to enable upload into a system.

A particular date format in the source file has the date value as 01/01/9999 and displayed in that exact format i.e. dd/mm/yyyy. When the macro creates the csv file, this value is also exported as 01/01/9999 but displayed as 01-Jan-99 i.e. dd-mmm-yy however when the csv file is closed and reopened, the date changes to 01/01/1999 still displayed as 01-Jan-99.

Any idea why this would be the case or how to stop this?


Code as below:

Application.ScreenUpdating = False
Rows("4:150").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\Uploads"
ActiveWorkbook.SaveAs Filename:= _
"C:\Upload " & Format(Range("h5"), "ddmmyyyy") & ".csv", FileFormat:=xlCSV, _
CreateBackup:=False
Range("H2:I150").NumberFormat = "dd-mmm-yy"
Range("AA2:AA150").NumberFormat = "dd-mmm-yy"
Range("AU2:AU150").NumberFormat = "dd-mmm-yy"
ActiveWorkbook.Save
Range("a1").Select
End Sub


Thanks in advance.