Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Percentage from Excel Data | Mailmerge | |||
Mail Merge Percentage some formatting correct some not | Mailmerge | |||
Can't apply formatting | Page Layout | |||
Merge with Percentage field looses formatting | Mailmerge | |||
Formatting a percentage mail merge field | Microsoft Word Help |