by Dermot Balson - William M. Mercer

Think Excel is immune to Y2K problems? Think again...

Run the code which follows in an Excel code module. It proves that, whereas Excel will save 8 digit dates correctly in CSV format if done manually, if you save a CSV file programmatically, it is saved with 2 year digits even if you typed in 4!

As a special bonus, it also may swap the day and month around.

Oops!

Sub CSVbug()

Workbooks.Add
With ActiveSheet
  .Cells(1, 1) = "11/11/2055"
  .Cells(2, 1) = "11/11/1915"
End With

Application.DisplayAlerts = False

'save as XLS
ActiveWorkbook.SaveAs filename:="test1.xls"

'save as CSV
ActiveWorkbook.SaveAs filename:="test1.csv", FileFormat:=xlCSV

'close
ActiveWorkbook.Close savechanges:=False

'open saved XLS
'this will be OK
Workbooks.Open "test1.xls" 

'open saved CSV
'first in Wordpad to see it in its raw state
Shell "wordpad.exe test1.csv"

'then in Excel
'it will NOT be OK
Workbooks.Open "test1.csv"

End Sub

---

[HOME] [TABLE OF CONTENTS] [SEARCH]