Incorrect date format when opening txt or csv files via macro

[proberts]

I refer to other posts on the same topic
Subject: Re: importing a text file - problem with dates "Americanising"
2/21/2006 9:47 AM PST
Subject: Re: opening text file in excel problem 2/10/2006 6:42 AM PST

Neither of the above seem to be actually answered satisfactorily.

My issue is the same

When I open a text file with a .csv or .txt extension in Excel 2003 - date
data appears correctly as expected.

However when I do it via simple macro - the american date format is invoked
and I get incorrect dates

e.g source data in .txt or .csv file

Field 1,Date,Field 3,Field 4
text,10/03/2006 08:04,text,text '( 10 March
2006)

Either of the following macros has the same result


Sub Test()
Workbooks.Open Filename:="test2.csv"
End Sub


Sub TestTxt()
Workbooks.OpenText Filename:="Test2.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, 4), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
Range("B2").Select
End Sub

The date now appears as follows

Field 1 Date Field 3 Field 4
text 03/10/2006 08:04 text text

I did not have this problem with Excel 2000.

Any explanations or automated workarounds would be appreciated

 

[Tom Ogilvy]

If you have renamed the file to have a ".txt" extension as you example shows,
then opentext should work.

If you leave it with a .csv extension, then your settings will be ignored
using Opentext.