Why does excel
represent the number correctly and word does not until it's formatted?
"Why" is always a difficult question to answer, particularly when you are
not the author of the software concerned. But...
You have almost answered your own question:
a. Excel stores the nmber internally using a floating point
representation which is not necessarily exactly what you typed in
b. Excel either applies default cell formats or one you explicitly apply
yourself, correcting the problem
c. when Word connects to Excel to get the data using OLEDB (the default
mechanism in Word 2002/2003) it retrieves the stored number, not the
displayed number, and nor does it retrieve the display format information.
d. Word has no knowledge of how you want to display the number so outputs
its version of the internal representation.
In my view, ideally OLEDB, or whatever mechanism MS standardised on for
internal Office data communications, would let you retrieve either the
internal representation, or the displayed values, or both, but it doesn't.
And you could well ask "Why?" about that, too...
You can revert to the DDE connection method (check
Word|Tools|Options|General|"Confirm conversions at open", go through the
connection process again, and select DDE when offered), which will typically
retrieve the display version of the data, but it does have its own problems
and limitations.
I suspect that MS would have a chance to do this stuff better if it put a
bit more effort into ensuring that intra-Office data communications could
all be XML-based and used a very standard set of data types and information
about representation.
Just my 2c worth...
Peter Jamieson
