Hi Jeroen,

I think this is a bit of a bug (foible may be a less controversial word)
in XL2000 - I guess that's what you're using. I don't notice the same
behaviour in XL2003 but I remember it being a real pain for me in XL2000.

You can get round this easily by specifying, when opening, how you want
each column to be interpreted. In the below example I've said columns 1
and 2 should be considered as DMY and column 3 should be MDY.

Sub openfiledates()

Dim myFieldInfo As Variant

myFieldInfo = Array(Array(1, xlDMYFormat), _
Array(2, xlDMYFormat), _
Array(3, xlMDYFormat))

Workbooks.OpenText Filename:="c:\temp\temp.txt", _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=myFieldInfo

End Sub

I hope this helps. My advice is (I'm sure you know but I'll say it
anyway) whenever you can, specify to any suppliers of text files
(clients, other departments etc.) that they use a format such as
DD/MMM/YYY i.e. where the month is written as text. That ensures avoid
any nasty surprises further down the line!!

cya,
Gareth


Jeroen Hofs wrote:
When I open a csv with a date in one "cell" excel allways interprets the date
as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel
interprets the date according to my system date settings. How can I tell
excel how to interpret the date within VBA when opening a CSV?