Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Powell[_2_] David Powell[_2_] is offline
external usenet poster
 
Posts: 28
Default A 'teachable' way to apply percentage formatting

I found I could use the following to apply percentages formatted in Excel or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default A 'teachable' way to apply percentage formatting

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news
I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Todd Todd is offline
external usenet poster
 
Posts: 41
Default A 'teachable' way to apply percentage formatting

No matter what I do, I can't get the % thing to work. Here's my current code:
{ MERGEFIELD "On_Time"*100\# "0.0%"}
and it does not show the proper percent. It shows 0.8% instead of 80.0%.
What in the world am I doing wrong? I have tried several variations of this.
When I changed my code to the MERGEFIELD "On_Time" part in the {}, I get no
result.

Does this only work on certain versions of Word? I use Word 2003 SP3.

Any help would be greatly appreciated.

thanks,
todd


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news
I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.



  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default A 'teachable' way to apply percentage formatting

The { MERGEFIELD } field needs to be nested inside a {=} field that does
the multiplication, e.g.

{ = { MERGEFIELD On_Time } * 100 \#0.0% }

where each pair of {} must be the special field braces that you can
insert using ctrl-F9

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
No matter what I do, I can't get the % thing to work. Here's my current code:
{ MERGEFIELD "On_Time"*100\# "0.0%"}
and it does not show the proper percent. It shows 0.8% instead of 80.0%.
What in the world am I doing wrong? I have tried several variations of this.
When I changed my code to the MERGEFIELD "On_Time" part in the {}, I get no
result.

Does this only work on certain versions of Word? I use Word 2003 SP3.

Any help would be greatly appreciated.

thanks,
todd


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news
I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.


  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Todd Todd is offline
external usenet poster
 
Posts: 41
Default A 'teachable' way to apply percentage formatting

Would there be any reason that after I merge the document, I get the
following in my new word document:
{ =0.813559322*100 \#0.0% }
BUT when I copy and paste that line, I magically get 81.4% ?


"Peter Jamieson" wrote:

The { MERGEFIELD } field needs to be nested inside a {=} field that does
the multiplication, e.g.

{ = { MERGEFIELD On_Time } * 100 \#0.0% }

where each pair of {} must be the special field braces that you can
insert using ctrl-F9

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
No matter what I do, I can't get the % thing to work. Here's my current code:
{ MERGEFIELD "On_Time"*100\# "0.0%"}
and it does not show the proper percent. It shows 0.8% instead of 80.0%.
What in the world am I doing wrong? I have tried several variations of this.
When I changed my code to the MERGEFIELD "On_Time" part in the {}, I get no
result.

Does this only work on certain versions of Word? I use Word 2003 SP3.

Any help would be greatly appreciated.

thanks,
todd


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.




  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default A 'teachable' way to apply percentage formatting

It sounds to me as if you are previewing the merge, rather than actually
merging to a destination (print/new document/email), and that what you
are seeing is the result of the { MERGEFIELD } field nested within the
code of the { = } field.

Which version of Word?

How are you actually performing the merge?

What if you use Alt-F9 to toggle field codes/results?

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
Would there be any reason that after I merge the document, I get the
following in my new word document:
{ =0.813559322*100 \#0.0% }
BUT when I copy and paste that line, I magically get 81.4% ?


"Peter Jamieson" wrote:

The { MERGEFIELD } field needs to be nested inside a {=} field that does
the multiplication, e.g.

{ = { MERGEFIELD On_Time } * 100 \#0.0% }

where each pair of {} must be the special field braces that you can
insert using ctrl-F9

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
No matter what I do, I can't get the % thing to work. Here's my current code:
{ MERGEFIELD "On_Time"*100\# "0.0%"}
and it does not show the proper percent. It shows 0.8% instead of 80.0%.
What in the world am I doing wrong? I have tried several variations of this.
When I changed my code to the MERGEFIELD "On_Time" part in the {}, I get no
result.

Does this only work on certain versions of Word? I use Word 2003 SP3.

Any help would be greatly appreciated.

thanks,
todd


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.

  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Todd Todd is offline
external usenet poster
 
Posts: 41
Default A 'teachable' way to apply percentage formatting

I figured out the issue, I was merging properly BUT I needed to hit alt-F9
after I merged in order to toggle. Even though all of the other fields
pulled correctly, I had to toggle one last time to make the {
=0.813559322*100 \#0.0% } turn into 81.4%.

thanks for your help.

"Peter Jamieson" wrote:

It sounds to me as if you are previewing the merge, rather than actually
merging to a destination (print/new document/email), and that what you
are seeing is the result of the { MERGEFIELD } field nested within the
code of the { = } field.

Which version of Word?

How are you actually performing the merge?

What if you use Alt-F9 to toggle field codes/results?

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
Would there be any reason that after I merge the document, I get the
following in my new word document:
{ =0.813559322*100 \#0.0% }
BUT when I copy and paste that line, I magically get 81.4% ?


"Peter Jamieson" wrote:

The { MERGEFIELD } field needs to be nested inside a {=} field that does
the multiplication, e.g.

{ = { MERGEFIELD On_Time } * 100 \#0.0% }

where each pair of {} must be the special field braces that you can
insert using ctrl-F9

Peter Jamieson

http://tips.pjmsn.me.uk

todd wrote:
No matter what I do, I can't get the % thing to work. Here's my current code:
{ MERGEFIELD "On_Time"*100\# "0.0%"}
and it does not show the proper percent. It shows 0.8% instead of 80.0%.
What in the world am I doing wrong? I have tried several variations of this.
When I changed my code to the MERGEFIELD "On_Time" part in the {}, I get no
result.

Does this only work on certain versions of Word? I use Word 2003 SP3.

Any help would be greatly appreciated.

thanks,
todd


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.


  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
David Powell[_2_] David Powell[_2_] is offline
external usenet poster
 
Posts: 28
Default A 'teachable' way to apply percentage formatting: feedback

A very belated thanks for this code.
Shortly after the post, I tried a few times to respond via notification
links, but they were broken for some reason.
I found this again through a keyword search.

Initially, it didn't work for me.
It was fine when
(i) I turned off display of field codes (using the Alt-F9 toggle), and
(ii) I ensured the field code item was an isolated "word". In particular,
it failed if I had a percentage as a parenthetical comment, thus: (93%).
So I apply the parentheses after formatting!

Word 2003

It would have taken an eternity for me to get that macro together. Thanks,
Doug.


"Doug Robbins - Word MVP" wrote:

If you added a button to a toolbar to run a macro that contains the
following code, if the selection is in a mergefield when that button is
clicked, it will make the necessary modification to the mergefield so that
the result will be displayed in the format #%

Dim mfield As Range
Dim fieldname As String
Selection.Words(1).Select
Selection.MoveStart wdCharacter, -1
Set mfield = Selection.Range.Fields(1).Code
If InStr(UCase(mfield), "MERGEFIELD") 0 Then
Selection.Cut
ActiveDocument.Fields.Add Selection.Range, wdFieldEmpty, "=" &
mfield.Text & " * 100 \# 0%"
End If
Selection.Words(1).Select
Set mfield = Selection.Fields(1).Code.Duplicate
mfield.Collapse wdCollapseStart
mfield.Move wdCharacter, 2
mfield.Paste
Selection.Range.Fields.Update


--
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
"David Powell" wrote in message
news
I found I could use the following to apply percentages formatted in Excel
or
Access in a Word mailmerge:

{ = MERGEFIELD "Fieldname" } * 100 \# "00%" },

where the braces are the 'magic' inserted by Ctrl-F9, and "Fieldname" is
the
field.
(From looking at other posts here, it seems I could omit one of the zeroes
in the format string above.)

Now: how can I encapsulate or package this incantation somehow, so that
others have a chance of applying it into their mail merges?
I can't get Autotext to work for me.



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
Formatting Percentage from Excel Data GeorgeA Mailmerge 1 April 4th 09 04:04 PM
Mail Merge Percentage some formatting correct some not kimhr Mailmerge 1 July 27th 08 08:15 PM
Can't apply formatting janet Page Layout 1 May 6th 06 03:22 AM
Merge with Percentage field looses formatting Rone Mailmerge 9 April 7th 06 06:07 AM
Formatting a percentage mail merge field Kylor Microsoft Word Help 5 March 18th 05 08:04 PM


All times are GMT +1. The time now is 02:59 PM.

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"