I've been trying to do a merge into a Word document like I do every year, but this year, with Office 2007 SP1, the results are not usable.

It's a simple spreadsheet including 2 columns of calculated values, formatted in Excel as Currency(2 decimal places). The names of these fields are "2009 Allotment" and "2010 Allotment". When inserted in the merge document, however, an "M_" is added as a prefix to the field name for some reason - can't find an answer for that(unless the reason is that it begins with a number), and when a value is inserted during merge, the number is shown to 4 decimal places instead of 2. It apparently makes no difference what the Excel formatting is (2 decimals or zero), since any merge gives me a 4decimal result.

What's happening here? How can I get a currency amount in Excel to merge properly into Word? Any help would be appreciated. Manual editing of several letters is at stake! Ugh!

I followed the instruction on the post, and the alternate mergefield formatting commands worked, although I have not been able to set Word up to take care of such things automatically.

Word evidently can't open the Excel file using DDE, and says it cannot open the file using ODBC either - no explanation given. It will only open the file as OLE DB Database file, which gives me consistent formatting problems with currency.

Why would you have to know arcane formatting codes to merge dollar amounts into a document. That's nuts!

The problem with ODBC could well be caused by the field name that acts weirdly. You might try giving the fields in the Excel workbook names that start with a letter and that don't contain spaces or interpunction.

Thanks for the idea. Tried it - no luck. Word insists on importing currency to 4 digits regardless of field name, whether they are taken from a named range vs. Sheet1, or any other reason. Guess I have to maintain a current knowledge of all formatting codes just to do a simple merge.

[quote name='Cotton' post='768162' date='30-Mar-2009 09:52']Thanks for the idea. Tried it - no luck. Word insists on importing currency to 4 digits regardless of field name, whether they are taken from a named range vs. Sheet1, or any other reason. Guess I have to maintain a current knowledge of all formatting codes just to do a simple merge.

Cotton[/quote]
Hi Cotton,

As discussed in microsoft.public.word.mailmerge.fields, if Word's mergefields are showing the values to 4 decimal places, that's because the values are *stored* that way in Excel. Had you rounded/trimmed the values to the appropriate number of decimal places in Excel, you wouldn't be having this problem.

Whether the formatting differences between Word and Excel are a 'problem' depends on your perspective. A distinct advantage that flows from Word working with the unformatted values is that you are then free to format them there as you like, without being constrained to whatever number formats appear in the data source.

Word's Help file has information on how to format field results, though it would have been helpful had MS made mailmerge field insertion a bit smarter by offering the available formatting options as part of that process. Nevertheless, to add a numeric picture switch to a mergefield named 'MyData', for example:
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

Note: The '\# 0' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values

The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00) zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

In the interests of keeping things as simple as possible, I've found that the solution lies in the way Word is importing the data. This is also the reason I've run across this problem in the 2007 Suite, and not in 2003.

If the spreadsheet used as a data source is saved in an Office 2003 Workbook format, Word will offer the option of importing the data via DDE, which preserves the Currency formatting. A data file saved as an .xlsx format file, however is only sourced through OLE, which does not (requiring formatting codes). In fact, Word will not import an .xlsx file via DDE, even though the option is shown in the import source dialog box if you click "Show ALL." So you are left with DDE only if you save the source in the previous format.

Summary - Office 2007 made merging more complex, not easier; restricted use of DDE in favor of OLE; and I still can't find a comprehensive list of all merge field codes in HELP, even though the DDE decision made them even more necessary.

[quote name='Cotton' post='769198' date='05-Apr-2009 00:51']and I still can't find a comprehensive list of all merge field codes in HELP[/quote]
Hi Cotton,

Perhaps you should try entering 'field format' in Word 2007's Help dialogue. Assuming your Word 2007 installation is up-to-date, on the first (of 4) pages, you'll see:
[attachment=83205:Help1.jpg]
Note the first item - Insert and format field codes in Word. That tells you pretty much all you need to know about how to format a mergefield. About 60% of the way down that item, the discussion turns to how to apply the various character (\*), numeric (\#) and date-time (\@) switches. Having a separate entry for mergefields, per se, would be superfluous, since they're no different regarding formatting than, say, a FILLIN field, REF field, formula (=) field, IF field, etc, none of which has its own section on formatting.

The search function on my installation is apparently brain dead, because nothing like that appears. Once I could see the "Help>Automation and Programmability>Field codes" I could navigate to the page you suggested using those topic headers. I then still had to expand every topic to get to the information I needed. This is not my idea of something "easy to use."

[quote name='Cotton' post='769256' date='06-Apr-2009 00:00']Tried it - that item does not appear anywhere ...when I do a search.

The search function on my installation is apparently brain dead, because nothing like that appears. Once I could see the "Help>Automation and Programmability>Field codes" I could navigate to the page you suggested using those topic headers. I then still had to expand every topic to get to the information I needed. This is not my idea of something "easy to use."

[quote name='Cotton' post='769198' date='05-Apr-2009 00:51']A data file saved as an .xlsx format file, however is only sourced through OLE ... Word will not import an .xlsx file via DDE, even though the option is shown in the import source dialog box if you click "Show ALL." So you are left with DDE only if you save the source in the previous format.[/quote]Hi Cotton,

This is not correct. Provided you've set the 'confirm file conversions at file open' option (Word Options|Advanced), you can select the DDE option when using an xlsx-format file.