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
>
Similar Threads
