merging from excell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ad - 10 Jan 2004 16:30 GMT

When merging from an excell sheet none of the number
formatting is retained. This used to be fine in Word
2000.

Reply to this Message

Graham Mayor - 10 Jan 2004 16:45 GMT

The default way that the two connect has been changed, and now Word is
expected to provide the formatting, but see the excel data section of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm and
http://www.gmayor.com/formatting_word_fields.htm to learn how to do it the
old way and a workaround respectively.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail gmayor@mvps.org
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> When merging from an excell sheet none of the number
> formatting is retained. This used to be fine in Word
> 2000.

Reply to this Message

yourbestfriend - 10 Jan 2004 23:46 GMT

The easiest method to keep all formatting when merging data from excel to word is to turn on the "prescsion as displayed" option in the options - Caluculation box under the tools menu.

This way all data as displayed on your worksheet will be merged into the word document and all formating such as forulma rounding up and deceimel placing etc are not lost in the transfer.

Reply to this Message

Graham Mayor - 11 Jan 2004 06:51 GMT

Thanks for reminding us of this option - an extra note on my web page is
indicated :)

It is certainly an alternative method if you have access to Excel (and not
merely the file) but I wonder whether it is always desirable to reduce the
precision of Excel's calculations in this manner - Microsoft obviously
doesn't think so as the option is off by default - and was this option
always available in Excel?

The main issue here is that Microsoft has changed the default method Word
uses to connect to its data, and thus Word is expected to provide any
formatting. This gives the user control over the appearance of the data in
the merge document. Alternatively it is still possible to use the older
connection method which eliminates the problem.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail gmayor@mvps.org
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> The easiest method to keep all formatting when merging data from
> excel to word is to turn on the "prescsion as displayed" option in
[quoted text clipped - 3 lines]
> the word document and all formating such as forulma rounding up and
> deceimel placing etc are not lost in the transfer.

Reply to this Message

- 12 Jan 2004 13:01 GMT

I have found that two digit decimals in Excel suddenly end
up being 15 digit decimals in the merged word document.
The way I solved it is with reference to article 304387 on
the Microsoft Knowledge Base articles. You have to
firstly right click on the merge field and select the
toggle (or press Alt + 9) and then define the 'switch'
after the field description. Thus in my case the toggle
field read {MERGEFIELD "amount"} and I changed it to
{MERGEFIELD "Amount"\#,###0.00} and it then limited the
merge to the required two decimals.

HIH.

Reply to this Message