Word 2003 & Excel

[Lew]

i use an Excel spreadshet to make a number of different calculations such as
percentages. I then mail merge the data to a report that is used in my
business. In the past, I've used Word 97 but have now upgraded to Word 2003

When I mail merge a Word 2003 document using Excel as the data source, I've
noticed that the merged data in my Word document has changed form. For
example, a field in the Excel spreadsheet might calculate a percentage to be
116%. In actuality, the percentage might be 116.3452%, but I set the cell
characteristics to "zero" places after the decimal point so that the
percentage is displayed as 116%.

When I perform my mail merge, the percentage is displayed as a decimal, plus
all of the places after the decimal point also show in the merged document.
For example, in the above case, my merge document shows 1.163452 even though
the number is correctly displayed in the Excel document as 116%. This
situation did not happen with Word 97, so I was caught off guard when it
occurred with Word 2003. I've tried everthing I could to modify the Excel
document so that the data is displayed as I need it to be in the merged Word
2003 document...but to no avail. It appears that the only solution that
works is to retype the data in each cell exactly the way I want it to appear.
This is not a practical solution. Is there a simple solution?

 

[Beth Melton]

They changed the default method in which the applications connect in
mail merge. They now use OLE DB since it's a direct connection and
more optimized than the previous default connection method, DDE.

However you can utilize the DDE method if you'd like which will enable
you to retain the formats in the Excel worksheet.

In Word
- Go to Tools/Options/General
- Turn on "Confirm Conversion at Open"
- Select your mail merge data source again
- When prompted select the DDE method

Otherwise you can add a numeric picture switch to your merge fields in
Word in order to format the results. For your percent it would be
something like:

{={Mergefield Amount} \# "0%"}

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/