[Bruce[_3_]]

Call me a dodo...but I have written a function that I want to use in my Word
Mail Merge document, but I can't work out how to call it. I have tried
using the Merge brackets {}, like {MyFunction()}, but that doesn't work.
It'll be something simple, that I just haven't thought of.

url:http://www.ureader.com/gp/1010-1.aspx

[macropod[_2_]]

Hi Bruce,

What sort of function? When is it supposed to run? Does it run when you select it via Alt-F8? Have you tried using a MACROBUTTON
field to trigger it?

--
Cheers
macropod
[Microsoft MVP - Word]


"Bruce" wrote in message .. .
Call me a dodo...but I have written a function that I want to use in my Word
Mail Merge document, but I can't work out how to call it. I have tried
using the Merge brackets {}, like {MyFunction()}, but that doesn't work.
It'll be something simple, that I just haven't thought of.

url:http://www.ureader.com/gp/1010-1.aspx

[Peter Jamieson]

If you mean that you want the function to "fire" for each record that
you merge, the "approved" way to do that is to use Word's MailMerge
events to call the function. Typically, you would use your function to
populate a Document variable, and use a { DOCVARIABLE } field in your
mail merge main document to insert that.

Out of the box, only one field code (DATABASE) will actually let you run
a bit of VBA and insert the result into the document during the merge,
and even that approach has many limitations. For example, in recent
versions of Word, there is no way to insert anything other than a number
or a date without inserting a paragraph mark. (see e.g.
http://tips.pjmsn.me.uk/t0004.htm , although that is a bit out-of-date
as some things in there no longer work).

Another slightly similar approach is if you just need to manipulate the
data in your data source a little - if the data source can be accessed
using a query language such as Jet SQL or Transact SQL then you may be
able to issue the necessary SQL in a VBA OpenDataSource call (but again,
there are limitations).

The other way to do it is "don't use MailMerge." As far as Microsoft is
concerned, it appears that anything that you can't easily do "out of the
box" is a candidate for a solution that uses .NET and/or VSTO, and
preferably Word 2007 content controls. But you can "roll your own" merge
using VBA.

[[
As background, at one time some people believed that there /must/ be a
field code that could be used to insert the result of a VBA (or earlier,
WordBasic) function directly into your document's text, but if there is,
no-one ever seems to have found it. I assume that people were hoping to
find something similar to the Excel mechanism, where you can write a new
Worsheet function in VBA and use it more or less the same way as any
built-in function. I looked at this whole area many years ago and came
to the following conclusions:-

DATABASE is the only field type that comes close. The others either do
not "fire" for each data source record, or do not have a mechanism that
would let you call VBA or pass anything to it. (LINK was the other main
candidate: I can't remember what the problem was there - probably the
degree of difficulty in writing something suitable to link to)

However, you could actually do it by writing a Word text converter DLL that
- took the data in the "subset" part of an INCLUDETEXT field
- called Word VBA to execute a piece of VBA
- returned the results by returning a piece of Word RTF that contained
bookmarked values or Document Variables

I did write such a thing - it had its own simple "interpreter" that
could do stuff such as add a number of days to a date, do simple string
operations ("left", "mid" etc.). It could also call a Word VBA function
using Automation and insert a result.

However, to use such a thing in the real world, you would typically have to
a. get it to work
b. convince the powers that be that it wasn't a security risk
c. distribute it to every computer that needed it
d. maintain it

As far as (d) is concerned, changes to Word over the years in response
to security issues or otherwise have made the approach, at best, less
reliable. I haven't tested it on recent SPs at all. Oh, and since
INCLUDETEXT does not pass Unicode text via its "subset" parameter, it
can only be used to manipulate ANSI/OEM text.
]]

Peter Jamieson

http://tips.pjmsn.me.uk

On 13/01/2010 07:58, Bruce wrote:
Call me a dodo...but I have written a function that I want to use in my Word
Mail Merge document, but I can't work out how to call it. I have tried
using the Merge brackets {}, like {MyFunction()}, but that doesn't work.
It'll be something simple, that I just haven't thought of.

url:http://www.ureader.com/gp/1010-1.aspx

[Bruce[_3_]]

Thanks for that, both of you - plenty to work with. I went to Word VBA
because I couldn't get NextIf to work. Is there a PrevIf? If I use NextIf
like { NextIf CurrentMergeField NextMergeField *do stuff* }, should that
work? Or have I got NextIf wrong? That is, can I compare a current record
field against the next record field? If not, how do I check if the next
record needs a new letter, or can just be displayed on this page?

url:http://www.ureader.com/msg/10107949.aspx

[Peter Jamieson]

I'd go with macropod's suggestion. Many if not all of your questions
will probably be answered there, but the overview is that Word Mailmerge
and its "field language" are quite limited, especially if you are used
to the much more complete merge language provided by WordPerfect.
Further, even if you use Word's MailMerge events, it is not
straightforward to "look forward" because each time you change the
Active Record, you trigger an event. So...

Is there a PrevIf?

No.

If I use NextIf
like { NextIf CurrentMergeField NextMergeField *do stuff* },
should that
work?

No, Nextif just does a comparison and either moves the current record
pointer 1 record or it doesn't. The text/fields after the nextif field
will reflect that.

Or have I got NextIf wrong? That is, can I compare a current record
field against the next record field?

You can't. All you can do is save the value of the field in the previous
record, and compare that with the value in the current record.

Peter Jamieson

http://tips.pjmsn.me.uk

On 14/01/2010 00:12, Bruce wrote:
Thanks for that, both of you - plenty to work with. I went to Word VBA
because I couldn't get NextIf to work. Is there a PrevIf? If I use NextIf
like { NextIf CurrentMergeField NextMergeField *do stuff* }, should that
work? Or have I got NextIf wrong? That is, can I compare a current record
field against the next record field? If not, how do I check if the next
record needs a new letter, or can just be displayed on this page?

url:http://www.ureader.com/msg/10107949.aspx

[Bruce[_3_]]

Excellent Peter - much appreciated. Having come from quite some programming
of MSAccess, MSWord is pathetic. Just a few more questions (following your
comment "I'd go with macropod's suggestion") - how do I use the MailMerge
event? Do I set up a Sub MailMerge_Execute, or ...? I looked everywhere I
could think of (obviously not enough) and could not find anything on
MailMerge events. And will the event code I write fire automatically, or
will the users have to hit a button or keys that essentially fire a macro?

url:http://www.ureader.com/msg/10107949.aspx

[Peter Jamieson]

I looked everywhere I
could think of

1. It used to be in the Help for Word VBA :-)

2. The following tiny example should give you enough info to get started
- it's in response to someone who wanted a record counter

a. create a VBA class module. Let's say it is called Class1.

Insert the following code:
'----------------
Public WithEvents app As Word.Application

Private Sub app_MailMergeBeforeRecordMerge(ByVal Doc As Document, _
Cancel As Boolean)
Doc.Application.StatusBar = _
"Merging record " & _
Doc.MailMerge.DataSource.ActiveRecord

End Sub
'----------------

b. In an ordinary module (i.e. not a Class Module), put the following code

'----------------
Dim x As Class1

Sub ShowMergeRecordCounter()
Set x = New Class1
Set x.app = Word.Application
End Sub

Sub UnShowMergeRecordCounter()
Set x = Nothing
End Sub
'----------------

c. run ShowMergeRecordCounter to switch on application-level event
handling.
d. do your merge. Notice that the status bar display is constantly
replaced by the "standard" display
e. run UnShowMergeRecordCounter to switch off application-level event
handling.


And will the event code I write fire automatically, or
will the users have to hit a button or keys that essentially fire a
macro?

3. At some point you will have to do what ShowMergeRecordCounter does.
So you have either to run that kind of code in Auto macro or other
"new/open document event code", or you have to usurp an existing
command, or you have to get the user to click something. And notice that
these are /application/ events, so if the user happens to set off two
merges, the events will fire for both of them.

4. Finally, AFAIK one of the events (MailMergeBeforeMerge) does not fire
unless the user initiated the merge via the Mail Merge Wizard in Word
2002/2003. I can't remember what the position is in Word 2007. So you
would typically have to use class initialisation code to do any
necessary initialisation instead.


Peter Jamieson

http://tips.pjmsn.me.uk

On 14/01/2010 23:48, Bruce wrote:
Excellent Peter - much appreciated. Having come from quite some programming
of MSAccess, MSWord is pathetic. Just a few more questions (following your
comment "I'd go with macropod's suggestion") - how do I use the MailMerge
event? Do I set up a Sub MailMerge_Execute, or ...? I looked everywhere I
could think of (obviously not enough) and could not find anything on
MailMerge events. And will the event code I write fire automatically, or
will the users have to hit a button or keys that essentially fire a macro?

url:http://www.ureader.com/msg/10107949.aspx

[Bruce[_3_]]

Thanks Peter. Great to write to someone who really knows his stuff.

url:http://www.ureader.com/msg/10107949.aspx