Calling a VBA function in MS Word

[TechScribe]

I have an existing document that I must fill in with information from an
Access database.

One field has so many IF ... THEN ... ELSE statements, MergeFields, and
Bookmarks that I can no longer read it.

So I opened the VBA Editor and created a function to replace it all. That
part was easy, taking five minutes because I stopped to pour another cup of
tea.

I created the function yesterday morning. I still cannot figure out how to
call that function to fill in the field on the document as though it were
calling a Merge Field.

Thank you for your time.
TechScribe

 

[Peter Jamieson]

There is no built-in mechanism using fields to insert the result of a /Word/
VBA macro into a document during a merge.

Possible approaches to this include:
a. you write the function in Access VBA, include it in an Access query,
then use that query as your datasource, connecting to it using DDE (which
lets you use the Access VBA function but has other drawbacks, e.g. won't let
Unicode data through)
b. you write the function as part of an Access query and use that as the
data source with whatever connection method you like.
c. you use Word MailMerge Events and VBA to set the value of a Document
Variable to the result of your function, for each record merged (more or
less). use a { DOCVARIABLE } field to get the data into Word
d. you use a Word VBA macro to perform one merge per record and do much the
same as (c)
e. similar to (b) but you use a DATABASE field to insert the result - only
really workable these days if the result is a number or a date.

If the function is sufficiently simple, only relies on other data in your
data source and standard Jet SQL information functions such as date, and you
are in a position to modify your Access database, (b) is probably the best
approach. (a) would not be bad - again if the data all comes from Access -
if you are already connecting using DDE. (c) is the "official" way to do VBA
during merges, but (d) is probably simpler where it is feasible. You can
find examples of most of these approaches by searching this group in Google
Groups (e.g. look for Peter jamieson activerecord for (d).

Peter Jamieson