Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem from opening dbf file in excel 2007 | Excel Discussion (Misc queries) | |||
File always opens with Date format set - Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 File Problems (Opening New Format) | Excel Discussion (Misc queries) | |||
switched date format opening dbf file | Excel Programming | |||
Date format when opening a .CSV file programmatically | Excel Programming |