Image of Navigational Map linked to Home / Contents / Search Excel, Y2K and CSV

by Dermot Balson - William M. Mercer
Image of Line Break

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



Written by: Dermot Balson
October '98

Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]