Date problem when opening a csv with dates in dd-mm-yyyy format.

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?

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?
>

Just to add to Gareth's reply.

Notice he changed the file name to .txt first (Filename:="c:\temp\temp.txt").

If you leave the extension .CSV, then VBA will ignore your code and do what it
wants (based on its USA centric rules).

(I'd rename the file to .txt and record a macro to get all the fields defined
correctly.)

Then if I get files of the same format, I'd just rerun this macro.


Gareth wrote:
>
> 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?
> >

--

Dave Peterson

Thanks a lot Gareth!
As I'm a beginner at this I would never have come up with this.
Regards,
Jeroen



"Gareth" wrote:

> 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?
> >
>

Thanks for clarifying Dave.
Regards,
Jeroen


"Dave Peterson" wrote:

> Just to add to Gareth's reply.
>
> Notice he changed the file name to .txt first (Filename:="c:\temp\temp.txt").
>
> If you leave the extension .CSV, then VBA will ignore your code and do what it
> wants (based on its USA centric rules).
>
> (I'd rename the file to .txt and record a macro to get all the fields defined
> correctly.)
>
> Then if I get files of the same format, I'd just rerun this macro.
>
>
> Gareth wrote:
> >
> > 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?
> > >
>
> --
>
> Dave Peterson
>