Problem importing dates using macro

[Ian Haining]

I'm having a problem with date formats when importing a text file into
Excel (Office 2000 SP3 on Windows 2000 SP3).
(File actually ends with a ".cal" extension - it's a calibration file
generated by a custom piece of software, but there's nothing fancy about it
- it's just a plain text file with fields separated by spaces)

When I import the text manually, there is no problem, but when I record a
simple macro to do the same task, the date gets treated as US format
instead of UK (e.g. 06/07/03 is coming out as 7th June 2003 instead of
the 6th of July).

This is only happening on one PC, and I cannot figure out what is different
on this PC compared to the others. The regional location, input locale and
default locale are all set to UK and the office language setting is also
set to UK (and I have removed the US altogether). I have various
combinations of Office and Windows versions including others with exactly
the same combination as on this PC, and all the others work correctly. I've
even tried completely removing
Office and re-installing it, but that made no difference.

Has anyone else experienced similar problem/ can anyone suggest
something else to try?


Ian Haining

 

[Lee Coward]

Hi Ian

This sounds like a known issue with the internal XL object model
implementation.

Workbooks.Open opens text files against US English, unless you set the Local
parameter to True, in which case it loads it against the XL UI language
(i.e. German if you're running the German SKU of XL) + control panel
regional settings. Basically, setting Local:=True on Workbooks.Open makes it
behave as if you used File/Open. The Local parameter was added to XL10 (i.e.
XL XP). The only caveat is that you need your .CSV file data to be in the
same date format as your control panel regional settings.

Try setting Local:=True and see if that solves your problem.

Regards,
Lee Coward
Microsoft VBA Progam Manager


I'm having a problem with date formats when importing a text file into
Excel (Office 2000 SP3 on Windows 2000 SP3).
(File actually ends with a ".cal" extension - it's a calibration file
generated by a custom piece of software, but there's nothing fancy about it
- it's just a plain text file with fields separated by spaces)

When I import the text manually, there is no problem, but when I record a
simple macro to do the same task, the date gets treated as US format
instead of UK (e.g. 06/07/03 is coming out as 7th June 2003 instead of
the 6th of July).

This is only happening on one PC, and I cannot figure out what is different
on this PC compared to the others. The regional location, input locale and
default locale are all set to UK and the office language setting is also
set to UK (and I have removed the US altogether). I have various
combinations of Office and Windows versions including others with exactly
the same combination as on this PC, and all the others work correctly. I've
even tried completely removing
Office and re-installing it, but that made no difference.

Has anyone else experienced similar problem/ can anyone suggest
something else to try?


Ian Haining