SOLVED

'Calling' a function from within a Word document

Brass Contributor

I know this is probably really basic but this will be my first real dive into Word VBA, I am quite experienced in Excel VBA, though.

 

I am having difficulty getting my head around how Word 'knows' that a 'block of text' is code, rather than just text of the document.

 

As my first 'excursion', within Word VBA, I am seeking to split a phrase, when it is passed, through mail merge, from an Excel spreadsheet to a Word document.

 

With apologies for i. the fact that my interface to this forum does not permit me to see all the proper formatting options for code and ii. the following is more of a 'storyboard' version of code, rather than the proper thing.

 

The relevant Excel spreadsheet is used to support a group of mail merge letters and this 'issue' is only relevant to one letter, so I do not wish to redesign the spreadsheet; I would prefer to handle the 'issue' within Word, through VBA code.

 

I will focus on two mail merge fields, within this question.  The 2 mail merge fields are called, for the sake of simplicity here, i) Relative_Name and ii) Relative_Relationship.

 

Relative_Relationship contains, for example, the value "Second Cousin Twice Removed of the Husband of my Second Cousin Once Removed", and, just to make up a name, Relative_Name contains "Walter Gerard Montague".

 

Now when I simply include the fields in the other letters I simply type -

 

Based on reviewing the online tree which has brought you to my attention, I can see that your tree includes «Relative_Name».  Your relationship to me: «Relative_Relationship».

 

and I would end up with a final merged text that reads -

 

Based on reviewing the online tree, which has brought you to my attention, I can see that your tree includes Walter Gerard Montague.  Your relationship to me: Second Cousin Twice Removed of the Husband of my Second Cousin Once Removed.

 

On this occasion, though I want to split the contents of the field Relative_Relationship into two halves, through passing it to a VBA macro, so that I would end up with -

 

Based on reviewing the online tree, which has brought you to my attention, I can see that your tree includes Walter Gerard Montague.  Walter Gerard Montague is the Husband of my Second Cousin Once Removed and you are his Second Cousin Twice Removed.

 

Now for the sake of the following let's call the VBA function, which I propose to write, Relationship_Phrase_Split.

 

Now here is the nub of my question, how does Word know I am invoking the subroutine when I write, in the mail merge document, something that maybe looks like -

 

Based on reviewing the online tree, which has brought you to my attention, I can see that your tree includes «Relative_Name».  Relationship_Phrase_Split(«Relative_Relationship», maybe some other fields from the Excel spreadsheet to control how the splitting is done, e.g. «Gender»).

 

I have only italicised, in the above, just to highlight where I am needing assistance to understand how to cause Word to process the code.  I am not asking for help on the split function, only the correct syntax for calling it, please.  I have made the document a .docm extension.

 

With thanks in anticipation.

 

Philip

Bendigo, Vioctoria

2 Replies
best response confirmed by PMHunt1955 (Brass Contributor)
Solution

@PMHunt1955 wrote:

I am having difficulty getting my head around how Word 'knows' that a 'block of text' is code, rather than just text of the document.


The difficulty is that Word doesn't have any way to "call" a macro automatically from the mere existence of a particular thing in the document text. The closest feature that exists is a MACROBUTTON field that contains the name of a macro, but it executes only when you double-click the field's display text. 

 

The solution to this problem is to write a larger macro that (a) runs the mail merge and then (b) carries out the split or other functions on the resulting document. The merge main document would need to contain some "marker" to tell the macro where the merged data resides.

 

Another solution, probably easier to handle, would be to modify the Excel source by adding new columns to contain the split data, with unique column names that would be used only in the affected document. The other documents wouldn't even "see" the added columns.

Dear Jay

 

Thank you very much for that  insight.  I was really wandering what I had missed, as I have been using Office for many years.  It leads me on to wonder about what good uses are available, for VBA, in Word, but that is a question for another day.  My nacent Word VBA skills are not yet at a stage where I can work out how to write a macro that calls mail merge, finds a 'marker' in the document and then performs the necessary split, so, for now, I will go down the route of adding fields to the Excel source.

If anybody at Microsoft is reading this, maybe you could look into developing a system, within Word, that does provide an ability to simply call VBA functions, during a mail merge, in the way that I have suggested in my original post.     

 

Best regards

 

Philip

Bendigo, Victoria                                                                                                                         

1 best response

Accepted Solutions
best response confirmed by PMHunt1955 (Brass Contributor)
Solution

@PMHunt1955 wrote:

I am having difficulty getting my head around how Word 'knows' that a 'block of text' is code, rather than just text of the document.


The difficulty is that Word doesn't have any way to "call" a macro automatically from the mere existence of a particular thing in the document text. The closest feature that exists is a MACROBUTTON field that contains the name of a macro, but it executes only when you double-click the field's display text. 

 

The solution to this problem is to write a larger macro that (a) runs the mail merge and then (b) carries out the split or other functions on the resulting document. The merge main document would need to contain some "marker" to tell the macro where the merged data resides.

 

Another solution, probably easier to handle, would be to modify the Excel source by adding new columns to contain the split data, with unique column names that would be used only in the affected document. The other documents wouldn't even "see" the added columns.

View solution in original post