How do I make mass format changes to mail merge fields?

[Jef Bray]

I have several MS Word documents that contain a high concentration of merge
fields (each document has upwards of 80-150 merge fields).

As experienced mail-merge users already know, formatting is not retained
from the data source, so I am expected to provide formatting instructions via
merge field "switches" such as \# $#,###.00 right within the merge field code
itself. This is fine for documents with just a few fields to adjust, but I'm
looking at hundreds and hundreds of fields here.

My problem is that typing formatting switches into each individual merge
field, one at a time, is very labour-intensive for documents such as mine. Is
there a way to apply these switches en-masse or perform some sort of search &
replace, taking merge field code into account?

It just seems very odd to me that a mature product such as MS Word butchers
decimal numbers so horribly (e.g. 40.1 is expressed as either
40.09999999999999 or as 40.10000000000000000001) and provide no way of
formatting these, except individually & manually.

Thanks in advance for any help you can provide.

ps: I am using SQL Server as a datasource, not that this should matter. The
datatype of the fields in question is "float"

 

[Graham Mayor]

If you can define the parameters around which switches are added to which
fields then it should be fairly simple to create a macro to add the switches
to the fields.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Jef Bray]

Graham,
Thank you for your reply. Yes, all of the merge fields in question end with
"_Perc" and need to be rounded to one decimal place.

The macro you speak of, is it simple enough to post an example here? *smile*

I'm a .NET developer but am completely brand new to MS Word macros.

Thanks
Jef

 

[Peter Jamieson]

Are you in a position to create views in SQL Server that convert the
float fields to formatted text fields, then use those views as the data
sources for your merge? Then you may be able to avoid this step
altogether - e.g. a Transact-SQL formula such as

'$'+convert(varchar,cast(round(UnitPrice,2) as money),1) [UnitPrice]

should convert a float value to a suitable $ value with comma separators.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Doug Robbins - Word MVP on news.microsoft.com]

Use:

Dim i As Long
Dim mfCode As Range
With ActiveDocument
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
If Mid(mfCode, InStrRev(mfCode, "_"), 5) = "_Perc" Then
mfCode.Text = mfCode.Text & "\# $#,###.0"
End If
End If
Next i
End With

Change the switch to the one that you actually require

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[macropod]

Hi Jef,

You can do this without a macro. Simply:
.. open the mailmerge main document
.. press Alt-F9 to expose the field codes
.. Use Find/Replace with:
. 'MERGEFIELD *_Perc' for the 'Find' text
. '^& ^92# $,0.00' for the 'Replace' text
. 'Use wildcards' checked
.. press Alt-F9 again to hide the field codes
.. save the mailmerge main document
.. run your mailmerge.

You'll note that I've used '\# $,0.00' as the formatting switch, instead of '\# $#,###.00' as you proposed. The reason for doing
this is that your rendition of the switch pads all values of less than $1,000 with spaces after the '$' and could lead to '$0.50',
for example, being displayed as '$ .50' (ie no '0' before the cents).