Date format on opening a csv file in Excel 2007
 

If I open the file manually from Excel, the dates appear in the correct
format (dd/mm/yy)
If I open the file from a macro, the dates appear in the wrong format
(mm/dd/yy)
I have tried copying the csv file to a txt file and the same happens - open
manualy and the dates are fine. Open from a macro and it is as though I have
flown across the Atlantic in 2 seconds!

Here is an extract of my code
DirPath = "C:\Program Files\Book-IT\CRM\"
fName = "TRM_Bookings.CSV"
fNameT = "TRM_Bookings.txt"
' need to rename file as a txt file
FileCopy DirPath & fName, DirPath & fNameT

Workbooks.OpenText Filename:="C:\Program
Files\Book-IT\CRM\TRM_Bookings.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), _
Array(15, 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:=True
Columns("H:H").EntireColumn.AutoFit

Date format on opening a csv file in Excel 2007
 

I have made some progress! If I change the code to array(4,4) instead of
(4,1) the date format is correct in column 4. The dates in this column are
just dates e.g. 05/11/2007.
However columns 8 and 9 are dates plus times e.g 05/11/2007 11:00.
Changing the code from array(8,1) to array(8,4) does not work.
Any ideas?

"Gleam" wrote:

If I open the file manually from Excel, the dates appear in the correct
format (dd/mm/yy)
If I open the file from a macro, the dates appear in the wrong format
(mm/dd/yy)
I have tried copying the csv file to a txt file and the same happens - open
manualy and the dates are fine. Open from a macro and it is as though I have
flown across the Atlantic in 2 seconds!

Here is an extract of my code
DirPath = "C:\Program Files\Book-IT\CRM\"
fName = "TRM_Bookings.CSV"
fNameT = "TRM_Bookings.txt"
' need to rename file as a txt file
FileCopy DirPath & fName, DirPath & fNameT

Workbooks.OpenText Filename:="C:\Program
Files\Book-IT\CRM\TRM_Bookings.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), _
Array(15, 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:=True
Columns("H:H").EntireColumn.AutoFit