importing date 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). 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

 

[Dave Peterson]

What's the name of your text file?

If it ends with .csv, then try renaming it to .txt.

Then you could have your macro explicitly set the format for that cell (when you
record the initial macro, you can choose date, dmy).

When you open .csv files in code, excel pretty much ignores any of your
formatting choices and uses USA settings.

Now the real question remains: Was it a .CSV file??????

 

[Ian Haining]

Thanks for the reply Dave. The file name varies but always ends with .cal (it's a
calibration file generated by a custom program). I have tried specifying the date
dmy format, but it doesn't make any difference - and the annoying thing as I said
before is that it's only this one PC that displays this behaviour.

What's the name of your text file?

If it ends with .csv, then try renaming it to .txt.

Then you could have your macro explicitly set the format for that cell (when you
record the initial macro, you can choose date, dmy).

When you open .csv files in code, excel pretty much ignores any of your
formatting choices and uses USA settings.

Now the real question remains: Was it a .CSV file??????

--
Regards
Ian Haining
Systems Administrator
Core Laboratories (U.K.) Ltd

----------------------------
NOTICE - This message contains information intended only for the use of
the addressee(s) named above. It may also be confidential and/or
privileged. If you are not the intended recipient of this message, you
are hereby notified that you must not disseminate, copy or take any
action in reliance on it. If you have received this message in error
please notify (e-mail address removed)

DISCLAIMER
Core Laboratories (U.K.) Limited does not represent that this
communication, including any files attached, is free from computer
viruses or other faults or defects. Core Laboratories (U.K.) Limited
will not be liable to any person for any loss or damage, including
direct, consequential or economic loss or damage however caused, and
whether by negligence or otherwise may result directly or indirectly
from the receipt or use of this communication or any files attached to
this communication.

 

[Dave Peterson]

I've never seen this problem (except with .csv files).

You may want to post your code and a few lines of input data--no attachments,
though.