The problem

You want a field that inserts the result of a VBA formula or expression into a Word document, but you have looked at the list of field types and you don't see a field called anything like { VBA } or { EVAL } or { EXECUTE }.

What's more, you want this field to execute for each record in the data source when you perform a mail merge.

One solution...

...may be to use a DATABASE field. But not if you're using Mac Word (2004, at any rate). And these days it can only really be made to work if the result is a date or a number.

Table of Contents

    A simple example

    Questions and Answers

    Alternative approaches

    So what advantages does the DATABASE approach have?

    Using MS SQL Server to execute queries (Still to be written)

    More examples (Only one example so far)

A simple example

It's easiest to show an example for Word 2002 or Word 2003. If you are using Word 97, 2000, or 2007, the following example should give you the general idea, but you will need to do things slightly differently.

Suppose you want to add 1 month to today's date. The Jet SQL to do this is simple, at least if you are working with "modern" dates in the "Western" Gregorian Calendar.

SELECT dateadd('m',1,date())

Note: Jet is the database engine that Access uses to maintain data in .mdb files. ACE is the renamed version of Jet used in Microsoft Access 2007. You don't actually have to have Microsoft Access to use the Jet/ACE engine, because it is installed with recent versions of Windows, Word/Office, and can in any case be downloaded from the Microsoft webs site (look for information about MDAC, "Microsoft Data Access", and information about Jet should not be far away).

To get Word to execute this in a DATABASE field, we have to use a data source that will make Word use the Jet SQL interpreter. In Word 2002/2003/2007 we can do that simply by specifying a Jet .mdb file as the data source. Let's suppose we have a valid .mdb in c:\i\i.mdb. Then the field can be:

{ DATABASE \d "c:\\i\\i.mdb" \s "SELECT dateadd('m',1,date())" }

If you do not have a valid .mdb you can use, there are various ways to create one (e.g. using Access, if you have it). However, because the Jet database engine works with other types of databases, including databases composed of plain text files, we can use specify one of those instead. In order to do that, use Notepad to create a .udl file called c:\i\i.udl and copy the following text into it, starting with the line [oledb].

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\i;Jet OLEDB:Engine Type=96;

If you are using Office 2007, you may have the ACE provider and not the Jet provider, in which case the following text should work:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\i;Jet OLEDB:Engine Type=96;

Yes, it's still "Jet OLEDB:Engine Type" and not "ACE OLEDB:Engine Type".

Using a .udl ("Universal Data Link") file tells Word to try to use OLE DB to open the data source. The .udl file specifies the OLE DB Provider to use, in this case the Jet/ACE database engine. Jet OLEDB:Engine Type=96 tells it to use the "Text files engine", which means that the folder c:\i is treated as a "database" that contains "tables", each of which is a delimited text file. So we can change the DATABASE field to be as follows:

{ DATABASE \d "c:\\i\\i.udl" \s "SELECT dateadd('m',1,date())" }

As you can see, we do not actually need to specify a "table".

Unfortunately, Word sometimes adds a paragraph mark to the result of the DATABASE field. However, because the result is a date, we can add a date format switch at the end of the DATABASE field, and that gets rid of the extra paragraph mark - as well as inserting the date in the format we want. For example.

{ DATABASE \d "c:\\i\\i.udl" \s "SELECT dateadd('m',1,date())" \@"DD/MM/YYYY" }

Once you have entered the field, select it and press F9 to update it. You should see the correct result. However, if you close the document and re-open it tomorrow, you will see that the data is unchanged - i.e., the DATABASE field is one of the fields that does not re-execute automatically when you open a document. You have to reselect it and press F9 to update it. During a merge, however, the field should update automatically.

This trick of using a format switch to eliminate a paragraph mark also works if the result is numeric and you use a numeric formatting switch. Unfortunately, there does not appear to be any way to eliminate the paragraph mark if you want an alphanumeric result. Because the paragraph is part of the field result, approaches such as nesting the DATABASE field inside a QUOTE field do not work. You can nest the DATABASE field in the \b or \f parameter of a MERGEFIELD field, e.g. { MERGEFIELD myfield \b "{ DATABASE ... }" } but of course the \b or \f parameter will only display/print if the MERGEFIELD is non-blank, which would not typically make for a good workaround. Using it as the text inside a \@ or \# format switch doesn't work either, and probably would not be a good idea anyway since you would have to be sure that the field did not return characters such as # and 0.

What if you want to add a month to a date field called "mydate" in a Word format data source? In that case, you can get the date using { MERGEFIELD mydate }, ensure it is in a recognisable format using the date switch \@ "YYYY-MM-DD", and include it in the Jet SQL formula by nesting the MERGEFIELD field in the DATABASE field as follows:

{ DATABASE \d "c:\\i\\i.udl" \s "SELECT dateadd('m',1,datevalue('{ MERGEFIELD mydate \@"YYYY-MM-DD" }'))" \@"DD/MM/YYYY" }

All the {} must be the field braces you can insert using ctrl-F9 - you can't use the ordinary characters from the keyboard.

When you construct your SELECT query using embedded fields rather than just literal text, you need to be aware of two problems:
 a. the resulting SQL may not be valid. For example, if the data source contains a "date" such as 32 January 2007, the above DATABASE field will result in a "database dialog or engine failure"
 b. a malicious user might be able to construct a SQL statement that behaves in a completely unexpected way. I don't have a particularly convincing example, but notice for example that the following field would execute whatever SQL code was in the bookmark called X:

{ DATABASE \d "c:\\i\\i.udl" \s "SELECT {REF X }" }

What about Word 97 and Word 2000?

If you are using Word 2000 or Word 97, you have to take a different approach, because by default, Word will get data from a .mdb database using DDE, which has to start Access. That's usually far too complicated for this kind of requirement. We can avoid that by using ODBC to open the .mdb. To do that, we need a .dsn file. A .dsn file has a similar function to a .udl file but specifies an ODBC driver, whereas a .udl file specifies an OLE DB provider.

To try this, create a file called c:\i\i.dsn that contains the following text:

[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
FIL=text
DriverId=27
DefaultDir=C:\i

Then use the following DATABASE field:

{ DATABASE \d "c:\\i\\i.dsn" \c "FILEDSN=c:\\i\\i.dsn" \s "SELECT dateadd('m',1,date())" \@"DD/MM/YYYY"  }

In this case, we have to add a \c parameter that tells Word what connection method to use. As far as I know, Word really gets all the information it needs about the connection method and the "database" to use from the \c parameter. However, you have to specify something in the \d parameter (at least in Word 2002 and later) and if, for example, you specified a .txt file in the \d parameter, Word would make a different set of assumptions about how to connect to that file, and ignore the \c parameter.

The above .dsn file will only work if you have an ODBC driver that is listed as "Microsoft Text Driver (*.txt, *.csv)" in the ODBC Administrator's list of ODBC drivers. It is possible that on some systems, particularly those that are not running the English Language version of Windows, this driver will have a different name (such as  "Microsoft Text-Treiber (*.txt; *.csv)" ). However, the same driver can have several different names (on the system I am using right now, I could use the English "Text Driver" name, the German "Text-Treiber" name, or the Spanish "Driver da Microsoft para arquivos texto (*.txt; *.csv)" name), so you may have the English language one as well.

Creating a DATABASE field that will work on different versions of Word

Apart from the problem mentioned above about the naming of the ODBC Text Driver, the approach that uses a .dsn file should work on all versions of Windows Word from 97 to 2007, as long as the appropriate ODBC driver is actually present and you get its name right in the .dsn file. Bear in mind that ODBC will not typically return Unicode characters

Can I set things up so that all the necessary files are in a single folder which I can then copy to any folder on a user's machine?

This can be done if you are using Word 2002 or later and Jet SQL, but you have to use a .mdb file with a name that is based on the name of the Word document. You may also find that the technique fails if the pathname of the folder containing the files reaches a certain length.

Suppose your Word document is called "myword.doc", then name your .mdb file "myword.doc.mdb". Then use the following nested fields

{ DATABASE \d "{ FILENAME \p }.mdb" \s "SELECT dateadd('d',1,datevalue('{ DATE \@"YYYY-MM-DD" }'))" \@"DD/MM/YYYY"  }

Test thoroughly!

The reason why you cannot use the .udl or .dsn approach described above to do this is because the .udl and .dsn file both have to name a specific folder, and that folder has to exist. When you simply copy the .udl or .dsn to a new folder the folder name does not change so unless that c:\i folder happens to exist on the target system, the DATABASE field in the new document will not work.  You could of course generate the .udl or .dsn "On the Fly" using VBA or some such.

There is another possible way to do this, but let's leave that until later, because it's about time for some...

Questions and Answers

I would guess that DATABASE fields are little used because MS Office users probably use other more data-oriented components of Office such as Access, Excel and Outlook to store and/or manipulate their data. If you tried the simple example above, some of these answers should already be clear but I'll cover some of the ground again anyway.

How can a DATABASE field solve this type of problem - it just inserts a table of data from an external file such as an Access database, Excel file, or text file, right?

Yes, that is true, but what the DATABASE field actually does is insert the results of an SQL query into the document. You can use any of the SQL syntax and functions provided by the data source that you specify in the DATABASE field, so if you specify a SQL Server database, you get to use TRANSACT-SQL queries, and if you specify an Access/Jet database you get to specify Jet SQL queries. And funnily enough, Jet SQL recognises many of the same functions that you can use in VBA.

Surely you have to get some results from a table or file somewhere? All I want to do is insert the result of a VBA expression

In "proper" SQL dialects, The SQL SELECT statement does not have to SELECT anything "FROM" anywhere, i.e. you do not have to have a FROM clause that specifies a data source. So for example you can just say

SELECT 2+2

which is a rather round-about way of getting 4.

Surely you still end up with a table? All I want is the result, inline with the rest of my text.

Yes, even if you execute SELECT 2+2, you will end up with a table consisting of 1 column and 2 rows. The first row contains the column name (which will be created by the SQL interpreter or Word in this case) and the second one contains "4". However, that only happens if you use the \h switch, which is inserted by default in the DATABASE field. If you leave it out, the query would only return a single cell, and in that case Word does not surround the value with a table cell - the result is just plain text.

What about security?

DATABASE fields do not appear to result in the "opening this document will cause the following SQL command to be executed" question that can pop up when your document is attached to a mail merge data source. I do not know whether that is because Microsoft has not yet noticed that executing the SQL in a DATABASE field involves a similar risk or whether they have decided not to plug the hole in this case. Perhaps the fact that you cannot use a DATABASE field to execute Access VBA via a DDE connection in Word 2002/2003 is a factor.

Otherwise, you will need access to the provider and any files referenced in the field. If you reference a SQL Server database, you will probably need read or execute permissions for a particular SQL Server "catalog".  

What limitations and drawbacks does this approach have?

Quite a lot, including:

 a. it is not viable on Mac Word (at least not version 2004) because there is no data source that comes with a full SQL interpreter. There is no Jet SQL, for example, and you cannot use another interpreter by connecting to an ODBC data source because Word 2004 cannot use ODBC. (The Help file is a bit ambiguous, but I've tried it, and I think Microsoft has confirmed the current position).

 b. there is no completely straightforward approach that will work for different versions of Word, even on Windows. The solution that uses a .dsn is probably the most portable one, but even that relies on the presence/absence of the necessary ODBC driver, and so on.

 c. Even though the SELECT statement may not reference any external data, the DATABASE field requires that you have an external file of some kind (i.e. even if it doesn't actually retrieve and data from it). That immediately complicates the distribution of any solution that uses this technique. In all cases you also rely on a provider or driver that is not strictly speaking a part of Word or Office. It might or might not be installed. However, you should be able to use the Jet SQL approach regardless of whether or not you have Access. The necessary Jet provider is either installed as standard with some versions of Office, or can be downloaded from the Microsoft web site and installed. From a technical perspective, you do not need Access to work with the Jet database engine and Jet SQL. As far as I am aware there are no licensing problems when using Jet from Word either, although you might want to check that.

 d. You cannot use DATABASE fields within Word tables (even in versions of Word where nested tables are possible, and even when the DATABASE field is returning a non-tabular result). It is probably inadvisable to use a DATABASE field in Headers/Footers as well, because Word re-executes fields in those areas quite frequently.

Where you want a DATABASE result in a table cell, you could try the following:

 - use a nested field outside the table to set a bookmark to the result of the DATABASE field:

{ SET myresult { DATABASE --your database field code-- } }

 - use a REF field inside the table:

{ REF myresult }

However, that approach will probably not work if you are merging to labels and your DATABASE field references data in your data source, because you would need to have a { NEXT } field before each { SET { DATABASE } } field, and you need the { NEXT } fields inside the table cells.

 e. In recent versions, Word sometimes adds a paragraph mark to the result of the DATABASE field, almost completely undermining its usefulness for returning single values. I have yet to work out in what circumstances it does this - I think some corruption occurs in the document or the template at some point, but the user experience is essentially arbitrary. One time your DATABASE field just returns the data you want. The next time you get an extra paragraph mark. It is possible to work around this for numeric and date results, but not as far as I know, for text results. As far as I can tell, Word 2000 did not used to have this problem, but it now does, suggesting that it has been created by a so-called "security update".

 f. If you build your SELECT statement dynamically then you have to be sure that the resulting statement is syntactically valid. For example, suppose you are using an ASK field to get a stock item code such as ABC1234 from the user and put it in a bookmark called StockItem. You know that the codes always have a three-letter prefix and you just want to grab the prefix from the bookmark rather than have the user enter it separately. So you use a DATABASE field with a nested REF field in the SELECT:

SELECT left('{ REF StockItem }',3)

If the user enters ABC1234, the query that Word sends to the SQL interpreter will be

SELECT left('ABC1234',3)

which should work. However, if the user enters A'BC1234 then the query will be

SELECT left('AB'C1234',3)

which the SQL interpreter should reject as invalid. In a one-off form, the user may be able to see that and correct the problem (if they know how), but during a MailMerge the problem cannot be avoided or fixed unless you are certain that your data will cause no problems (and in my view that is not a very good assumption to make in most cases).

As far as I know there is no way to use SQL procedures and parameters as a workaround for this drawback.

 g. You cannot execute user-defined VBA functions via Jet SQL, only simple VBA expressions. So whatever it is you want to calculate, you either have to do it without looping, or you have to use a version of SQL that lets you invoke more sophisticated functions. You can do that if your database connects to a SQL Server database because you can return the result of a multi-statement TRANSACT-SQL stored function. I will provide an example of this in a future version of this page.

 h. DATABASE fields are probably going to execute quite slowly compared to some other methods

 i. In Word 2002 and later, when your DATABASE field is in a MailMerge Main Document, Word sometimes gets confused between the Merge Data Source, and the Data Source for the DATABASE field. I have to say I have not yet investigated this "feature" thoroughly, but I believe that where you are simply using a DATABASE field to return a single value, this interference probably does not occur.

 j. In most cases, there is likely to be a better way of doing what you're trying to do than using a DATABASE field.

 k. Some of the approaches described here rely on behaviour that Microsoft might at some point regard as security loopholes and decide to close up.

Have I put you off yet? If so, that's probably a good thing because you will immediately look for a better and more stable approach that will last forever :-). So...

Alternative approaches

I may eventually expand some parts of this section. For the moment, you can...

Do calculations in a MailMerge data source

If you are performing a mailmerge from a data source that lets you define queries or views (such as Access, SQL Server, and Oracle) then as long as the result you want depends only on data that can be retrieved via that data source, you can use that data source's query language to create a query or view that contains the calculation you need, and use that as the data source for your merge. For example, if your data source is an Access table called mytable, and you want to insert today's date + 1 in your merge output, you can create a query with SQL along the following lines:

SELECT [m].*, dateadd('d',1,date()) AS [tomorrow]
FROM   [mytable] [m];

or even just

SELECT [m].*, 1 + date() AS [tomorrow]
FROM   [mytable] [m];

In this case, because the results are inserted using MERGEFIELD fields, you should not encounter the problem whereby DATABASE fields insert an additional paragraph mark. however, be sure that the query language and data source can actually do what you need (e.g. return Unicode characters or strings longer than 128 characters, etc.)

Do calculations in a SQL statement outside the data source

If you are performing a MailMerge and you are using a data source that you cannot modify (e.g. the data source is SQL Server but you do not have the permissions necessary to create new queries), you may be able to do what you need by

 a. creating an Access database with tables linked to the SQL Server table(s) you need to use, then creating a query in that database and using that as the data source. Again, be sure that the combined data sources do what you want - you may find that Word will not work with such connections, or that Unicode data is not returned.

 b. issuing the necessary SQL in a Word (VBA) OpenDataSource method call. You may encounter the same limitations as in (a), plus an additional limitation which is that the SQL statement is limited to around 255 characters or 512 characters, depending on the version of Word, the data source, and connection method.

Use macropod's solutions

If you want to do numeric or date calculations, you should seek and consider macropod's work, because it uses "ordinary" fields that do not reference external data sources, can be placed pretty much anywhere in a document, whether it is a mailmerge main document or not, should be executed when you expect, and provides solutions for inserting "moveable" dates such as Easter, dates that fall on a particular weekend in a month, and so on. At the time of writing, there are:
Fields for date-related calculations in http://www.wopr.com/w3tfiles/10-249902-DateCalc.zip
fields for numeric calculation in www.wopr.com/w3tfiles/10-365442-WordFieldMaths.zip

Use VBA (or another language) to insert the information you want

This option is low in this list because I don't think you would be looking at this page if your preferred way of solving this type of problem was to use VBA and either an "Auto" macro or some form of user-initiated action such as a MACROBUTTON field, keystroke, or toolbar button to run the necessary VBA. However, if that's what you want to do, one useful approach is to use a { DOCVARIABLE } field in the main document which then inserts the value of a Word Variable that you set in VBA.

When MailMerging, you might combine that technique with one of the following two approaches:

 a. use VBA to perform "One merge for each record in the data source" or

 b. use the Word object model's MailMerge events and VBA to insert informaiton during the merge.

(b) is the "official" approach, but most people who are only diving into VBA because they absolutely have to are likely to find (a) rather simpler to understand.

So what advantages does the DATABASE approach have?

I've outlined the approach, given an example, mentioned a number of problems and alternatives, but what are the advantages of the DATABASE solution, if any?

 a. it does the thing you need, i.e. calculates a result and inserts it into the document at the point where you inserted the field. During a MailMerge, it also does it when you need it. VBA is arguably much harder to work with - first, you have to decide how and when to run the VBA, and then you have to get it to put its result in the right place. Yes, you can use a MACROBUTTON field to provide a way for the user to trigger some VBA, but MACROBUTTONs are not "pressed" when you perform a MailMerge - in that case, to use VBA, you either have to use Word's MailMerge Events, which are a further step beyond the territory familiar to most users, or you have to merge to a new document then posprocess the results, or you have to perform one merge for each record in the data source. Each approach has its own drawbacks.

 b. You don't appear to have to worry about VBA security options. Yet. But it may be that the system I use is not sufficiently tightly secured to see some of the problems that users in a typical business network environment would see.

 c. Although you can do quite sophisticated calculations using simpler Word fields, it often requires a lot of fields. In my view, an expression in a DATABASE field, although by no means ideal, is in many cases a much clearer way and arguably more easily

Using MS SQL Server to execute queries

(To be written)

More examples

All these examples use either a Jet .udl file (as in the examples above) or a SQL Server connection. You'll need to add your own \@ date format switch or \# numeric format switch as appropriate.

Today + 2 days avoiding Saturday and Sunday

Using Jet

{ DATABASE \d "c:\\i\\i.udl" \s "SELECT dateadd('d',choose(weekday(date(),1),2,2,2,2,4,4,3),date())"  }

(More to be written)

	Enter your search terms Submit search form
	Web tips.pjmsn.me.uk