Excel VBA: importing CSV with dates as dd/mm/yyyy
- by Michael Smith
ello
I understand this is a fairly common problem, but I'm yet to find a reliable solution.
I have data in a csv file with the first column formatted dd/mm/yyyy. When I open it with Workbooks.OpenText it defaults to mm/dd/yyyy until it figures out that what it thinks is the month exceeds 12, then reverts to dd/mm/yyyy.
This is my test code, which tries to force it as xlDMYFormat, and I've also tried the text format. I understand this problem only applies to *.csv files, not *.txt, but that isn't an acceptable solution.
Option Base 1
Sub TestImport()
Filename = "test.csv"
Dim ColumnArray(1 To 1, 1 To 2)
ColumnsDesired = Array(1)
DataTypeArray = Array(xlDMYFormat)
' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x
Workbooks.OpenText Filename:=Filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray
End Sub
test.csv contains:
Date
11/03/2010
12/03/2010
13/03/2010
14/03/2010
15/03/2010
16/03/2010
17/03/2010
Thanks
Michael
