Merging to Word

[Ian Coates]

I am creating a Word mail merge document from an Excel worksheet source.
Everything is fine except that currency amounts appear as 76.5 instead of
76.50. How can I correct this?

TIA

Ian

 

[Michael J. Malinsky]

You need to format the field in Word. I have done this without a problem.

In Word, right click on the merge field and select Toggle Field Codes. At
the end, just inside the right curly bracked, type something like:

\* #,##0.00 or \* $#,##0.00

Do a search in Word help for something like numeric switches.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Ian Coates]

Hi Michael

Unfortunately the filed switch (\*) does not support formatting of this
nature. I had already looked at the help file for this and got nowhere. I
wondered if something needed to be changed in Excel to achieve this.

 

[Michael J. Malinsky]

Well then I'm stumped. I've never had a problem using field switches to
format data merged from an Excel file.

The issue is that just because you made the number look "pretty" for the
sheet, the underlying data is still unformatted and is what is merged into
Word. This is more of a Word mail merge issue. Have you tried posting your
question on the Word NGs? I think there's one specifically for mail
merging.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Ian Coates]

Yes, I've tried microsoft.public.mailmerge.fields which sounded the most
likely, but so far haven't had a reply.

 

[Dave Peterson]

Debra Dalgleish posted this for a different question, but I bet you could modify
it:

In the Mail Merge, after you select your Excel file as a data source,
you should see a 'Confirm Data Source' dialog box.
(If you don't see the dialog box, change the setting in Word --
under Tools>Options, General -- add a check mark to
'Confirm Conversion at Open')

From that list, choose 'MS Excel Worksheets via DDE (*.xls)', and your
formatting will be retained.

If you have to connect through a different source, you can format the
fields in the Word document. For example, to specify a number of decimals:

1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
{ MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
For example:
{ MERGEFIELD FieldName \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document


========
Mike's suggested formatting statement was:
\* #,##0.00 or \* $#,##0.00

which looks enough different (* instead of # and no quotes) to make it worth a
try.