none
Changing Merge Field via Code RRS feed

  • Question

  • I'm using Word 2010.

    Using the Mail Merge functionality, I'm automating report documents.  This has been going pretty well until today when I get a requirement that a dollar amount needs to be expressed as words, as well as numbers (sort of like what you see on a check).

    I found code that does the translation, but I don't know how to "attach" it to the merge field so that it will automatically convert the field's value, which is a number, to the text representation created by the function.

    How do I do this?

    TIA, 
    Clif

    Tuesday, June 10, 2014 3:52 PM

Answers

  • Hi Clif,

    Please refer How to automate Word with Visual Basic to create a Mail Merge to attach value to MailMerge.Fields Property (Word).

    Please refer ACC: How to Convert Currency or Numbers into English Words to convert a dollar amount into English Words.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 11, 2014 9:43 AM
    Moderator
  • You can do that by inserting a second mergefield, with a DollarText or CardText switch. However, Microsoft’s DollarText field switch doesn't express a value as 'five dollars and 10 cents' – it returns 'five and 10/00'. Similarly, Microsoft’s CardText field switch doesn't support decimal values. The following construction handles both issues, where 'Amount' is your data field's name:

    {QUOTE {SET Val {MERGEFIELD Amount}}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"} and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}

    More sophisticated field coding will also insert the 'and' after hundreds, etc., that the MS switches also omit.

    Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field construction are all required.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, June 11, 2014 12:25 PM

All replies

  • Hi Clif,

    Please refer How to automate Word with Visual Basic to create a Mail Merge to attach value to MailMerge.Fields Property (Word).

    Please refer ACC: How to Convert Currency or Numbers into English Words to convert a dollar amount into English Words.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 11, 2014 9:43 AM
    Moderator
  • You can do that by inserting a second mergefield, with a DollarText or CardText switch. However, Microsoft’s DollarText field switch doesn't express a value as 'five dollars and 10 cents' – it returns 'five and 10/00'. Similarly, Microsoft’s CardText field switch doesn't support decimal values. The following construction handles both issues, where 'Amount' is your data field's name:

    {QUOTE {SET Val {MERGEFIELD Amount}}"{=INT(Val) \* CardText} dollar{=INT(Val)-1 \# "s;s;"} and {={=MOD(Val,1)*100 \# 0} \* CardText } cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}

    More sophisticated field coding will also insert the 'and' after hundreds, etc., that the MS switches also omit.

    Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field construction are all required.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, June 11, 2014 12:25 PM