Overcome Excel to Word mailmerge number format problems
Q: I have been using Excel and Word to mailmerge data for years and yet have been completely stopped in my tracks by the problems I have had in my present task. I'm using Office 2002.
I have a large amount of data, some text, some currency, some dates, some general number data stuff. My problems are as follows:
1. Some of the dates (formatted as the equivalent of "d mmmm yyyy" fields) import from Excel into Word quite successfully. Others import as the date value codes (eg 58947 or whatever). Why? I have tried retyping, overtyping etc and the only way I can seem to fix it is by formatting the cells as plain text, which then means I can't sort on them by date order.
2. Some of the currency fields which are set to GB sterling with currency symbol in Excel, 2 decimal places, then import into Word with lots of extra decimal places which may or may not be un-rounded. It means that each time I want to check my figures I have to go back into a large Excel sheet and see whether I have got £34,950.95 or £34,950.94699999! Again, why is this and what can I do to fix it?
3. Similar problem to (2) - percentages seem to gain an extra shedload of decimal places, which also means I have to go back and check the original data.
4. Some of the currency fields import across into Word with the currency symbol, others do not. *scratches head to work this one out*
The frustration factor in this is huge as this data is used to provide correspondence which has to meet a legal compliance test. We cannot - absolutely never, not ever, no way - have any of the info incorrect.
All suggestions gratefully received. I am rapidly losing my sense of humour with it!
A: Can't help on WHY this is happening*, but I've seen it lots and I know how to fix it.
What you need to do is to add format switches to the MAILMERGE fields in the Word document. Let's say you have a field called 'Cost' which leaves Excel as £19.99 or similar, but turns up in Word as 19.991234. Here's how to fix it.
In the word document, start by keying Alt+F9 to expose all the field codes. Look for the one that says:
{MERGEFIELD Cost }
There may or may not be quotes around the word 'Cost' - it doesn't matter either way. Put your cursor between the end of the word 'Cost' (after the quotes if there are any) and before the closing curly bracket and type the following:
\# £##.00
The full field should now read:
{MERGEFIELD Cost \# £##.00}
This next bit is IMPORTANT. Do a CRTL+A and then a F9 to update all fields. Now do Alt+F9 to switch back to field values instead of codes.
That should sort it.
There are a whole range of field switches like this. Word help has very good coverage - look for 'time and date pictures' or 'number pictures' or if all else fails, send Clippy looking for 'field switches' and you'll find it from there.
* Post-publication comments from Doug Klippert suggests that this is due to the use of OLE DB rather then DDE. Read more about this at his site here.