Old automation bug reappeared?

[Richard Stott]

I am having problems with a macro which inappropriately converts data into
alternative formats while importing from a CSV file. Seem to be at least 2
issues although they may actually be the same problem -

Numbers apparently randomly convert to dates (Big problem given that the
application is recording drug levels in patients) - 0.6 in the CSV file
converts to 00/01/1900.

Some dates convert to US style despite the regional settings being for UK.
This is moderately obvious as we get dates which are in the future - 8
/1/2007 (8th January) in CSV file presumably converts internally into an
internal reference number but is presented as 1/8/2007 (1st August!).

I remember something similar happening for automated data imports on a
previous version of Excel (one of the 90s versions) and there was a patch
which solved it. At the time it took me some time to convince the hospital's
IT specialists that there was a problem & I ended up having to go looking for
the patch myself. Unsurprisingly, they seem similarly unable to help this
time too.

The spreadsheet has recently moved to a PC running Excel 2002 (Version
10.4302.4210 with SR-2 applied) and the issue has come back. Is there a fix?

 

[Jan Karel Pieterse]

Hi Richard,

Some dates convert to US style despite the regional settings being for UK.
This is moderately obvious as we get dates which are in the future - 8
/1/2007 (8th January) in CSV file presumably converts internally into an
internal reference number but is presented as 1/8/2007 (1st August!).

VBA speaks American, but you can specify the date format of the IMPORT in one of
the steps of the wizard. Record a macro to set the date format order according
to your file and update your VBA accordingly, so it should do what is expected.

I remember something similar happening for automated data imports on a
previous version of Excel (one of the 90s versions) and there was a patch
which solved it. At the time it took me some time to convince the hospital's
IT specialists that there was a problem & I ended up having to go looking for
the patch myself. Unsurprisingly, they seem similarly unable to help this
time too.

I doubt this is something that is fixed with a patch, it is by design.

Stephen Bullen has a chapter from his book at his site which deals with
international issues like this one:

Look for the book called Excel 2002 VBA programmer's reference on:
www.oaltd.co.uk

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Richard Stott]

--
Richard

Hi Richard,


VBA speaks American, but you can specify the date format of the IMPORT in one of
the steps of the wizard. Record a macro to set the date format order according
to your file and update your VBA accordingly, so it should do what is expected.


I doubt this is something that is fixed with a patch, it is by design.

Stephen Bullen has a chapter from his book at his site which deals with
international issues like this one:

Look for the book called Excel 2002 VBA programmer's reference on:
www.oaltd.co.uk

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

I still think it is a bug (design like that is what gets MS products a bad
name!).

It will import whole columns of data and only change one or two items. Yes
they may be the only ones which are valid dates when swapped round but it
should be consistent and handle all data the same. Doesnt even do the same
thing each time it imports an identical data file (Is that a good definition
of a software error?).

Either
- it is seeing UK format & accepting it as fitting the defined format of the
cell
Or
- it is insisting on US format (despite the format of the destination cell)
& should reject all 'incorrect' format data.

Richard