Excel CSV Import - Not seeing Date and formating it

[Lurb]

Hello,

In code I am opening a CSV file with a date in - excel XP is then formatting the date as general and not as date.

I have used the Exactly the same code and csv files on Excel 97 and Excel 2000 with no problems.

Any ideas why it isn't picking it up as a date?

Regards
Lurb

 

[PHV]

Have you tried the macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Lurb]

The macro recorder doesn't help.

I have tracked it down to the fact Excel XP has a locale selector for the date format selector when formatting cells. If I set Office to US then it works fine but when it is set to UK it reads the CSV as a US date and finds the 28/6/04 as an invalid date.

I don't know how to get around it, but this is the problem.

Any ideas how to get around this?

Regards
Lurb

 

[xlbo]

Set the whole lot of dates to text and convert AFTER getting them into excel - either thyat or convert csv to tab delimited and use the text import wizard to specify the date format

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question