Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

    Rick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't have Office 2007 so I can't help with the field name problem.

    To preserve the number formatting, see this post. The methods described are for Word 2003 and before, but you can ask Word 2007 what the equivalent ribbon commands are.

  3. #3
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

    Cotton

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Cotton

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [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.
    Attached Images Attached Images
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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."

    Cotton

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [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."

    Cotton[/quote]
    Hi Cotton,

    In that case, might I suggest you:
    a ) make sure you've got the Office 2007 SP1 update and all later updates installed, and, if so
    b ) repair your Office installation (Word Options|Resources|Run MS Office Diagnostics).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    Lounger
    Join Date
    Jul 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All I can say is, yes, the option appears, but in my particular case, Word informed me that it could not open the file - no further enlightenment offered. Must have been sunspots or something.

    Cotton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •