[Excel 2003] Open Text File problem
I recorded the following code which is to open a text file as a workbook.
Code:
Workbooks.OpenText Filename:= FN, Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 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, 4), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1)), TrailingMinusNumbers:=True
My problem lies in the Eight column.
In my text file this column contains the date in the format of
dd/mm/yyyy hh:mm:ss
When I open this by hand the date is converted correctly, thus a date of 12/09/2008 15:33:21 is converted as 12/09/2008 15:33:21
However when I run the code above the date gets converted into 09/12/2008 15:33:21
Can anyone offer any suggestions as to why this is happening and, more
importantly, how to correct it.
Re: [Excel 2003] Open Text File problem
Is your Regional Settings configured as MM/DD/YYYY?
Re: [Excel 2003] Open Text File problem
Hi Hack,
My regional Settings are dd/mm/yyy
I actually have managed to create a work around.
As I was opening the text file and then processing the
information to put into the spreadsheet I was able to
import that particular field as Text, then in the code
which retrieved that field to put onto my spreadsheet
I used CDATE to convert to a date field, and it correctly
converted.
I would still like to know what was causing the problem in the first place.