6.3 Merging documents with data | Microsoft Office Automation with Visual FoxPro

Merging documents with data

Mail merge is another of the killer functions that put word processors on everybody s desk. The ability to write a document once, then merge it with data repeatedly to produce personalized documents made sense to businesses, academics, and home users, too.

Word offers a variety of ways to merge data and documents. The best known is the most complex using the built-in mail merge ability. However, it s also possible to merge documents by using Find and Replace and by combining the built-in mail merge capabilities with some manual labor. We ll take a look at each of these approaches here and tell you why we think the third approach is the best suited to Automation.

Word s mail merge structure

Mail merge has been included in Word for many versions. Interactively, there s a wizard called the Mail Merge Helper (see Figure 7) that guides users through the process. Even with this tool, though, setting up a new mail merge document, connecting it to data, and generating results is not simple. We ve both spent many hours on the phone walking relatives and friends through the process.

Behind the Mail Merge Helper, there are a number of objects. Help lists no fewer than eight different objects whose names begin with "MailMerge." But it s not the complexity of the object model that leads us to recommend alternative approaches; it s the fragility of the connection between the document and the data.

When you indicate that a Word document is a mail merge document, you specify the data source for the merge. It can be another Word document, a text file, or any of a variety of database files, including FoxPro, of course. For most data sources, Word uses ODBC to read the data.

If the data file is deleted or moved or something happens to the ODBC driver, the merge stops working. Many people using FoxPro 2.x tables in mail merge got a nasty surprise when they installed Service Pack 3 for VFP 6 (or anything else that installed ODBC 4.0) because it disabled the ODBC driver for FoxPro 2.x tables and failed to automatically substitute the VFP driver for it. Mail merges that had been working for years failed.

Figure 7. Mail merge. This dialog walks interactive users through the steps needed to use Word s built-in mail merge facility.

The need to deal with ODBC drivers and connections makes this approach harder and, especially, riskier than it needs to be. Unless you re dealing with extremely large tables, there are other, simpler ways to solve this problem. If you are dealing with large tables, plan to control the ODBC aspects yourself rather than relying on what s on the user s machine. (To learn about managing ODBC and connections, check out the book s sister volume in the Essentials series, Client-Server Applications with Visual FoxPro 6.0 and SQL Server 7.0.) The key thing you ll need to do is pass the connection string to the OpenDataSource method of the MailMerge object (discussed later in this chapter).

Substituting data with Find and Replace

One alternative to Word s mail merge is to use the Find and Replace objects. Since these were discussed in detail in Chapter 5 (see "Search and replace"), we ll talk here only about the specific issues involved in using them for merging documents and data.

The basic idea is to create a document that contains the desired result except for a set of strings that are to be replaced by data from your database. Alternatively, you can use a template from which you create a document, then replace the strings.

The main issue is making sure that you only replace what you mean to replace. When you re working interactively, this isn t a problem because you can see each match before you agree to replace it. With Automation, however, you need a better solution. One possibility is to enclose the strings with a special character or characters. For example, you might surround the strings to be replaced with angle brackets (like "<name>") or exclamation points ("!name!"). Then, the code to replace the string would look something like this:

#DEFINE wdReplaceAll 2

oRange = oDocument.Range(0,0)

WITH oRange.Find

.Text = "!Name!"

.Replacement.Text = TRIM(Employee.First_Name) ;

- " " + TRIM(Employee.Last_Name)

.Execute( , , , , , , , , , , wdReplaceAll)

ENDWITH

The biggest problem with this approach is trying to find an appropriate special character to delimit your strings. You really need to be sure it s something that won t appear elsewhere in the document. While it s unlikely that a special character like an exclamation point would surround text, there s always the off chance that a user might choose to do something strange.

You can use a table to drive this approach. Consider putting the strings and formatting to be found and substituted into a table, and having a single method to perform the search and replace. Nonetheless, while searching for and replacing delimited strings is a viable solution to merging data into documents, we still think there s a better way.

Drop back 10 yards and punt

So what s a body to do? There s Word with a perfectly good mail merge engine, but the need to use ODBC to access FoxPro tables is a serious impediment. On the other hand, the rest of the mail merge facilities are really useful. The solution is to avoid the ODBC aspects while taking advantage of everything else Word has to offer.

The way to do that is to create the data source for a mail merge on the fly, sending FoxPro data to Word, and attaching it to the document just long enough to merge it. This strategy is appropriate when the amount of data to be merged is small to moderate, but it may need to be reconsidered for extremely large data sets. (However, realize that no matter what approach you use, the data has to be sent to Word somehow, so this method may be as good as any other and it does afford you more control over the process and less likelihood of trouble caused by end users than the traditional approach.)

The documents involved in mail merge

Once you take ODBC out of the picture, mail merge involves two or three documents. The first is what Word calls the main document. That s the document that contains the text of the letter, envelope, labels, or whatever it is you re trying to create. Most of this document looks like any other document. The exception is that, in a few places, it contains fields special markers that indicate that something is to be substituted at that location. Word actually offers a wide range of fields, including such things as the date, time, and page number. For mail merge, we re specifically interested in fields of the type MergeField.

The second document in a mail merge is the data source. This is the document that contains the data to be substituted into the fields. It contains an ordinary Word table with one column for each MergeField. In the strategy we describe, we ll build this document on the fly.

The third document is optional. It s the result created by merging the main document with the data source. We prefer to merge to a new document rather than directly to the printer, but there may be situations where you choose to skip this step.

The objects involved in mail merge

The main object in mail merge is, in fact, called MailMerge it s accessed through the MailMerge property of Document. MailMerge s Fields property references a MailMergeFields collection, made up of MailMergeField objects these objects represent the mail merge fields in the main document. When the document is attached to a data source, the DataSource property of MailMerge accesses a MailMergeDataSource object. Its DataFields property references a collection of MailMergeDataField objects that provide information about the fields in the data source. MailMergeDataSource also has a FieldNames property that references a MailMergeFieldNames collection with information about the field names for the data.

If this seems like a lot of objects, that s because it is, but in the strategy described here, you ll need to work directly with only the MailMerge and MailMergeFields objects.

Creating a main document

The first step is creating a main document. There are several ways to do this, not all involving Automation. Your users may simply create a main document using the Mail Merge Helper. The problem with that approach, of course, is that such documents will have data sources attached, but there are some solutions (discussed in the next section, "Attaching a data source").

Users can also create main documents manually by inserting mail merge fields by choosing Insert|Field from the menu. The dialog shown in Figure 8 appears. To add a mail merge field, choose Mail Merge in the left column and MergeField on the right, then type the field name in the Field codes box, as shown in the figure.

Figure 8. Inserting mail merge fields. Users can build mail merge documents manually rather than using the Mail Merge Helper.

Of course, you can build main documents with Automation just like other documents. In fact, you can also use a hybrid approach, initially setting up the document with Automation, then allowing a user to edit it.

To add a mail merge field to a document, use the Add method of the MailMergeFields collection. It calls for two parameters, as follows:

oDocument.MailMerge.Fields.Add( oRange, cField )

oRange	Object	Reference to a range where the mail merge field is to be added.
cField	Character	The mail merge field to be inserted.

Attaching a data source

One of the things that makes the Mail Merge Helper so helpful is that it provides a list of the fields in the data source and lets you choose from that list as you create the main document. Figure 9 shows part of the Mail Merge toolbar with the Insert Merge Field dropdown open, showing a list of the fields from TasTrade s Employee table.

Figure 9. Adding fields interactively. When a main document is attached to a data source, you can add fields by choosing them from the Mail Merge toolbar.
Using header sources for field lists

If we want users to be able to create and edit main documents, we need a way to provide them with a list of fields, even though we don t want to create permanent connections between main documents and data sources. Several methods of the MailMerge object let us set up field lists.

In fact, there are two kinds of connections a main document can have to data. It can be connected to an actual data source that contains records available for merging. However, a main document can instead be connected to a header source, a document that provides only field names for the merge, but no actual merge data.

The advantage of a header source is that it s small and easy to create. We can use a header source to provide users with a list of fields while creating or editing the main document, but wait to create the complete data source until the user is ready for the actual merge. We can also create the header source and hide it from the user, when that s an appropriate strategy. (That might be appropriate where users are known to delete files when they shouldn t.)

Listing 2 is a program that attaches a header source to a main document, based on the field list in a table or view. (It s CreateHeaderSource.PRG in the Developer Download files available at www.hentzenwerke.com.) The key to the whole process is the call to the CreateHeaderSource method of MailMerge the rest is just typical FoxPro string manipulation. You might call this routine like this:

DO CreateHeaderSource WITH oDocument, _SAMPLES+"TasTrade\Data\Employee", ;

"C:\Temp\EmployeeHeader.DOC"
Listing 2. Creating a header source. This program generates a header source on the fly from any table or view and attaches it to a document.
* CreateHeaderSource.PRG

* Create a header source for the current document

* based on a table or view

* Assumes:

* Word is open.

LPARAMETERS oDocument, cCursor, cDocument

* oDocument = the document for which a header source is to be created.

* cCursor = the filename, including path, of the table or view.

* cDocument = the filename, including path, where the

* header source document is to be stored.

* Check parameters

IF PCOUNT()<3

MESSAGEBOX("Must provide table/view name and document name")

RETURN .F.

ENDIF

IF VarType(oDocument) <> "O"

MESSAGEBOX("No document specified")

RETURN .F.

ENDIF

IF VarType(cCursor) <> "C" OR EMPTY(cCursor)

MESSAGEBOX("Table/View name must be character")

RETURN .F.

ENDIF

IF VarType(cDocument) <> "C" OR EMPTY(cDocument)

MESSAGEBOX("Document name must be character")

RETURN .F.

ENDIF

LOCAL nFieldCount, cFieldList, aFieldList[1], nField

* Open the table/view

USE (cCursor) AGAIN IN 0 ALIAS MergeCursor

SELECT MergeCursor

* Get a list of fields

nFieldCount = AFIELDS( aFieldList, "MergeCursor" )

* Go through the list, creating a comma-separated string

cFieldList = ""

FOR nField = 1 TO nFieldCount

IF aFieldList[ nField, 2] <> "G"

* Can't use General fields

cFieldList = cFieldList + aFieldList[ nField, 1] + ","

ENDIF

ENDFOR

cFieldList = LEFT( cFieldList, LEN(cFieldList) - 1 )

* Attach the header to the document

oDocument.MailMerge.CreateHeaderSource( cDocument, , , cFieldList )

USE IN MergeCursor

RETURN

The resulting header file is simply a one-row Word table, each column containing a fieldname.

When you open a main document interactively and the header source or data source is missing, Word insists that you either find the missing source or take action. In Word 2000, when the same thing happens with Automation, Word simply opens the file and detaches the header source or data source itself.

Unfortunately, in Word 97, when you open a main document with Automation and the data source is missing, Word insists on your finding the missing data source, though it s surprisingly inventive if you point to the wrong file.

The OpenHeaderSource method of MailMerge attaches an existing header source to a main document. OpenDataSource attaches an existing data source to a main document. Both take long lists of parameters, but in each case, only the first is required it s the name of the header/data source file, including the path. Here s an example:

oDocument.MailMerge.OpenHeaderSource( "C:\Temp\EmployeeList.DOC" )
Using a data source at merge time

The header source allows your users to create their own main documents using a list of merge fields. Header sources contain no data you need the ability to create and attach a complete data source on the fly. The CreateDataSource method lets you build a new data source. As with CreateHeaderSource, you build a Word table, then attach it to the main document. Most of the work is pretty straightforward. Listing 3, included as CreateDataSource.PRG in the Developer Download files available at www.hentzenwerke.com, creates and attaches a data source to a document. It accepts the same three parameters as CreateHeaderSource in Listing 2. Because the slowest part of the process is sending the actual data from VFP to Word, be sure to create a cursor or view that contains only the data you need for the mail merge before you call CreateDataSource. Do your filtering of both fields and records on the VFP side. The EditDataSource method opens the DataSource associated with a main document. If it s already open, it activates it.

Listing 3. Build a better data source. This program creates a data source on the fly. Rather than dealing with ODBC, send just the records and fields you need to a Word data source when you re actually ready to do a mail merge.
* CreateDataSource.PRG

* Create a data source for the current document

* based on a table or view

* Assumes:

* Word is open.

LPARAMETERS oDocument, cCursor, cDocument

* oDocument = the document for which a header source is to be created.

* cCursor = the filename, including path, of the table or view.

* Data should already be filtered and sorted.

* cDocument = the filename, including path, where the

* data source document is to be stored.

* Check parameters

IF PCOUNT()<3

MESSAGEBOX("Must provide table/view name and document name")

RETURN .F.

ENDIF

IF VarType(oDocument) <> "O"

MESSAGEBOX("No document specified")

RETURN .F.

ENDIF

IF VarType(cCursor) <> "C" OR EMPTY(cCursor)

MESSAGEBOX("Table/View name must be character")

RETURN .F.

ENDIF

IF VarType(cDocument) <> "C" OR EMPTY(cDocument)

MESSAGEBOX("Document name must be character")

RETURN .F.

ENDIF

LOCAL nFieldCount, cFieldList, aFieldList[1], nField

LOCAL oWord, oRange, oSourceDoc, oRow, oTable

* Get a reference to Word

oWord = oDocument.Application

* Open the table/view

USE (cCursor) AGAIN IN 0 ALIAS MergeCursor

SELECT MergeCursor

* Get a list of fields

nFieldCount = AFIELDS( aFieldList, "MergeCursor" )

* Go through the list, creating a comma-separated string

cFieldList = ""

FOR nField = 1 TO nFieldCount

IF aFieldList[ nField, 2 ] <> "G"

* Can't use General fields

cFieldList = cFieldList + aFieldList[ nField, 1] + ","

ENDIF

ENDFOR

cFieldList = LEFT( cFieldList, LEN(cFieldList) - 1 )

WITH oDocument.MailMerge

* Attach the data to the document

.CreateDataSource( cDocument, , , cFieldList )

.EditDataSource

oSourceDoc = oWord.ActiveDocument

oTable = oSourceDoc.Tables[1]

oRow = oTable.Rows[1]

* Now open the data source and put the data into the document

SCAN

WITH oRow

FOR nField = 1 TO nFieldCount

DO CASE

CASE TYPE( FIELDS( nField )) = "C"

* Get rid of trailing blanks

.Cells[ nField ].Range.InsertAfter( ;

TRIM( EVAL(FIELDS( nField ))))

CASE TYPE( FIELDS( nField )) = "G"

* Do nothing

OTHERWISE

* Just send it as is

.Cells[ nField ].Range.InsertAfter( EVAL(FIELDS( nField )))

ENDCASE

ENDFOR

ENDWITH

oRow = oTable.Rows.Add()

ENDSCAN

oRow.Delete()

oSourceDoc.Save()

ENDWITH

USE IN MergeCursor

RETURN

Performing the mail merge

Once you ve jumped through all the hoops to get the data there, actually performing the mail merge is the easy part. Just call the MailMerge object s Execute method and poof! the main document and the data source are merged to a new document. This is all it takes:

oDocument.MailMerge.Execute()

Of course, you probably want to exercise more control than that over the merge. Various properties of the MailMerge object let you set things up before you call Execute. The two you re most likely to deal with are Destination and SuppressBlankLines. SuppressBlankLines is a logical that indicates whether lines in the document that are totally empty should be eliminated. The default is .T.

Destination determines where the merge results are sent. The default is wdSendToNewDocument (0), our preference. Our choices are wdSendToPrinter (1), wdSendToEmail (2), and wdSendToFax (3). There are several properties, all of which begin with "Mail," dedicated to particulars of the case where results are sent to e-mail.

Determining the document type

The MailMerge object has a couple of other important properties. State lets you check, for any document, what role it plays in the mail merge process. Table 3 shows the possible values for State.

Table 3. What kind of a document am I? MailMerge s State property tells you what kind of document you re dealing with.

Constant	Value	Constant	Value
wdNormalDocument	0	wdMainAndHeader	3
wdMainDocumentOnly	1	wdMainAndSourceAndHeader	4
wdMainAndDataSource	2	wdDataSource	5

MainDocumentType tells what kind of main document you have. Table 4 shows the values this property can take.

Table 4. Merge document types. The MainDocumentType property classifies documents into the various kinds of merge documents you can create.

Constant	Value	Constant	Value
wdNotAMergeDocument	-1	wdEnvelopes	2
wdFormLetters	0	wdCatalog	3
wdMailingLabels	1

Rescuing abandoned mail merge documents

You may be faced with main documents that have been detached from their data sources and for which you don t have access to the appropriate tables. It turns out that it s quite easy to go through these documents and build a header source or a data source for them, as well.

Listing 4 shows ExtractDataSource.PRG (also included in the Developer Download files available at www.hentzenwerke.com), which traverses the MailMergeFields collection of the document to create a list of fields, then uses that list to create a data source (albeit an empty one).

Listing 4. Restoring data sources. When main documents get detached from their data sources, this program can create an empty data source that allows the document to be edited.
* ExtractDataSource.PRG

* Process an existing document and create a data source document

* based on the fields used on the document.

LPARAMETERS oDocument, cSourceName

* oDocument = the existing mail merge document

* cSourceName = the filename, including path, for the data source

#DEFINE wdNotAMergeDocument -1

* Should check parameters here. Omitted for space reasons

WITH oDocument

IF .MailMerge.MainDocumentType = wdNotAMergeDocument OR ;

INLIST(.MailMerge.State, 0, 1)

* Need to create a data source

* Go through fields and create a list

LOCAL oField, cCode, cField, cFieldList, cHeaderName

cFieldList = "'"

FOR EACH oField IN .MailMerge.Fields

cCode = oField.Code.Text

* Parse out extraneous information

cField = ALLTRIM( STRTRAN(STRTRAN(cCode, ;

"MERGEFIELD",""), "\* MERGEFORMAT", "") )

IF NOT cField+"," $ cFieldList

cFieldList = cFieldList + cField + ","

ENDIF

ENDFOR

IF LEN(cFieldList) > 1

cFieldList = LEFT(cFieldList, LEN(cFieldList)-1) + "'"

ENDIF

* Now create a data source document.

.MailMerge.CreateDataSource( cSourceName, , , cFieldList )

ENDIF

ENDWITH

RETURN