Opening CSV files with VBA/macro

[Vasco]

XL2000:
When a open this CSV file through EXCEL normal interface,
the files gets correctly formatted when it opens without
asking any questions. A date field present is interpreted
and formatted as dd-mm-yy.
If I record these actions and run then run the macro, the
file gets the wrong format in this "date field". It treats
the dates as mm-dd-yy! So half of the dates in the file do
not get recognized as dates (dates where day >12)
Again, this last behaviour only happens if you use the
macro to open it.

Glad to hear any help!

Vasco

 

[keepitcool]

Vasco,

use Workbooks.OpenText method..

and have a look at all the arguments in VBA help.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Vasco]

Thanks

I was in the process of doing that but, unfortunately,
that does not work still! If i use the

FieldInfo:=Array(Array(1, xlDMYFormat)...) it will still
fail. It insists in reading MM/DD/YY instead of DD/MM/YY.

I must say that my date also have a time but i do not
think that should be a problem.

Again, only in VBA because if you do it through EXCEL it
will work. More complicated than it seems...

I can try to perform an online conversion using datavalue
and timevalue but is exquisite that you are not able to
duplicate a simple action in Excel with VBA.

Vasco

 

[Tom Ogilvy]

My experience is that if the file has a .CSV extension, then the settings in
OpenText are ignored. (this may be improved in xl2002 - I can't say). The
way around this is to rename the file to .Txt and use OpenText with the date
column set to be interpreted as you wish. You can turn on the macro
recorder while you open it manually and choose the appropriate settings.
This will record the opentext method and define the appropriate settings.