Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Jef Bray Jef Bray is offline
external usenet poster
 
Posts: 1
Default How do I make mass format changes to mail merge fields?

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"
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default How do I make mass format changes to mail merge fields?

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Jef Bray wrote:
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"



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Jef Bray[_2_] Jef Bray[_2_] is offline
external usenet poster
 
Posts: 1
Default How do I make mass format changes to mail merge fields?

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

"Graham Mayor" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Jef Bray wrote:
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"




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default How do I make mass format changes to mail merge fields?

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

Jef Bray wrote:
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"

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP on news.microsoft.com Doug Robbins - Word MVP on news.microsoft.com is offline
external usenet poster
 
Posts: 407
Default How do I make mass format changes to mail merge fields?

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

"Jef Bray" wrote in message
...
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

"Graham Mayor" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Jef Bray wrote:
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"








  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How do I make mass format changes to mail merge fields?

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).

--
Cheers
macropod
[MVP - Microsoft Word]


"Jef Bray" Jef wrote in message ...
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"

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge mass mailing with e-mail sentence and attachment BL Mailmerge 1 August 23rd 08 06:29 AM
How do I format mail merge fields? Black Doctor Mailmerge 7 February 16th 07 07:33 AM
Format a mail merge doc address 2 fields are needed insome ltrs Smitty Mailmerge 3 August 8th 06 11:49 PM
How to merge Excel chart for mass Word Mail Merge for employees hmboomer Mailmerge 3 June 9th 06 10:08 PM
mail merge format fields Graham Mayor Mailmerge 1 January 27th 06 10:58 AM


All times are GMT +1. The time now is 10:57 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"