Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
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 |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using a VBA function in my Word Mail Merge document
Thanks Peter. Great to write to someone who really knows his stuff.
url:http://www.ureader.com/msg/10107949.aspx |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail merge function | Mailmerge | |||
Why does Mail merge function freeze Word? | Mailmerge | |||
how to perform auto correct function on a mail merge document | Microsoft Word Help | |||
Mail merge - Ask / If function | Mailmerge | |||
How do I revert the mail merge function in Word XP to older versio | Mailmerge |