Visual FoxPro can tell programs such as Microsoft Word what to do—so you don’t need to build a word processor into your applications.

By Michael P. Antonovich

Have you ever used a screwdriver as a hammer? How about as a chisel? Or maybe even as a wedge? The point is, we often use tools we're comfortable with to perform tasks those tools weren't designed for. As a FoxPro programmer, perhaps you use FoxPro as the solution for every problem you encounter. Although it's possible to do this most of the time, you can create better applications by combining the best features of different tools—just as a master craftsman knows when to use a hammer, screwdriver, or saw.

This article shows how to use OLE Automation to produce a hybrid document from within Visual FoxPro to control another application—Microsoft Word. With the increased ease of combining applications through OLE Automation, it makes more sense to use the best-of-breed tools—rather than try to make one tool be all things for all applications.

Opening a Word object
Each application that supports OLE Automation also identifies which topics it supports as an automation server. In the case of Word, only a single object (called Basic) is supported. It refers to the WordBasic instruction set. First, you open a link to the Basic object using the Visual FoxPro function CREATEOBJECT(), the same as any of VFP's base objects:

MyWordDoc = CREATEOBJECT("Word.Basic")

If you type this command in the Command window, the cursor returns to the next command line (after a short delay accompanied by some disk activity). Nothing else happens visually on the screen. However, this command creates an object variable that provides a way for VFP to communicate with Word using WordBasic commands. Unlike opening multiple applications through the Start button in Windows 95, or the Project Manager in Windows 3.x, you can't switch to this instance of Word using the Alt+Tab keys.

TIP: While Win95 hides the instance of Word running in the background during OLE Automation, Windows for Workgroups 3.11 and Windows 3.1 display the Word window so you can watch as OLE Automation makes its edits. For debugging purposes, you might want to open Word first. VFP uses that instance of Word, rather than starting a new one. You can then step through your code, one line at a time, and see the effect in the Word document by toggling between Word and VFP.

After an OLE object is defined, set its properties and call its methods. Think of these commands as no different from those used to reference properties or methods of any VFP object. For example, to open a document in Word after defining the object, use the WordBasic command FileOpen:

MyWordDoc.FileOpen("SAMPLE.DOC")

WordBasic normally uses a statement more like this:

FileOpen .Name = "SAMPLE.DOC"

VFP expects to have values passed as parameters to the method call. In fact, the required form is similar to calling a FoxPro object's method, and passing the file name as the argument. Suppose you merely want to print this document after opening it. Call another method, the FilePrint command from WordBasic, and you can print the current document:

MyWordDoc.FilePrint

How can you determine what methods and properties exist? One way is to obtain a copy of the Microsoft manual Using WordBasic, which describes each command. You can obtain online help by opening the Help menu. In Word 6, the main Word Help screen contains an option called Programming with Microsoft Word on the Word Help Contents page. (Loading this Help file is optional. If it isn't present on your system, use the install disks to add it.) In Word 7, select WordBasic Reference on the Contents page of Help.

Most WordBasic commands can be used in OLE Automation. Furthermore, each command can have a number of additional parameters to control its operation. However, some commands can't be used—especially those that require interaction between the user. An example is a dialog, such as FilePrintSetup, because OLE Automation doesn't provide direct user interaction. (For more on WordBasic's parameters, see this issue's Ask Advisor column on page 82.

To close the object, use this code:

MyWordDoc.AppClose

While this command closes Word as an application, it doesn't release the object variable MyWordDoc. To release the memory used by this object, follow the closing of an object with a statement like:

RELEASE MyWordDoc

(As a practice, you should release object variables after you finish using an object—no matter what its source is.)

While these commands allow you to open a document in Word and print it, the real power of OLE Automation comes from interacting with the document to create compound documents using the best features of Word and VFP. The next example shows some of the possibilities.

Stating the problem
Suppose you need to create a document that requires a great deal of special formatting—more formatting than FoxPro's report writer can easily handle. In fact, it's really a job for a word processor, but you need to include data in this document from a Visual FoxPro table. The old way to do this was to create a Mail Merge data file, and then use the Mail Merge feature in Word to combine the data with a template. This might work if all you need to do is merge data. But what if your needs are more complex?

Suppose, for example, you want to eliminate sections from the document (or even entire paragraphs) if certain table fields are empty or null. Furthermore, suppose you want to append a FoxPro report, or maybe an Excel chart, to the end of the document before printing it. OLE Automation can easily solve these types of situations.

This example introduces basic OLE Automation concepts that, in many ways, mimic the capability of mail merge files. However, I also explore how to remove unwanted sections of text, as well as to how to append a FoxPro report to each copy of the document.

Setting up a Word document
The first step in setting up a Word document is to create the text of the document. (It's possible to create the entire document via OLE Automation. However, for most uses, and especially for this article, let's assume you're starting with a base document previously created with Word.)

Determine where you want to change or customize the text. Figure 1 shows this initial document. It indicates spots where text will be replaced by enclosing a short text string with angle brackets, so you can quickly find them.

Figure 1: CREATE THE WORD DOCUMENT FIRST—In many cases, you should create the basic document before beginning the OLE Automation process.

Next, identify these text strings with unique bookmark names. This lets Word Basic commands quickly locate specific spots in the document. Also, by selecting a "label" before defining the bookmark, you can automatically replace the marker text with the data to be inserted.

To define a bookmark, select the text including any punctuation (or angle brackets as in my case), and select Word's Edit | Bookmark option. This displays a dialog that lets you add, delete, and go to a defined bookmark. Figure 2 shows the first bookmark defined on the customer's full name.

Figure 2: DEFINING BOOKMARK—Using Word's Bookmark feature, it's easy to find particular points in the document and substitute for them.

While the bookmark name can be any text, consider using the name of the field that will replace the bookmark. This makes it easier to follow what's happening. Of course, bookmark names can't contain spaces, but neither should field names. Also, don't include the paragraph mark at the end of a line in the text selected for the bookmark—unless you plan to add your own paragraph breaks or line returns. Word stores information about formatting in the paragraph mark. After this setup is completed, the program included with this article shows how you can cycle through all the bookmarks and retrieve the appropriate data from your table's fields.

While most of the time you should define bookmarks on groups of characters excluding the paragraph mark, sometimes you might need to include the mark. A typical example occurs in this document. The third line of the address section provides a second address line. Many addresses have only a single address line. If the program were to replace the bookmark label with an empty string from the table field, it would leave a blank line in the address. We would prefer to eliminate that line completely. We can do this easily by including the paragraph mark in the bookmark, then deleting the bookmark when the second address field is empty.

Figure 3 shows the completed list of bookmarks for our simple letter.

Figure 3: BOOKMARK LIST—Using the set of bookmarks shown, you can perform a mail merge via OLE Automation.

Next, prepare a paper and pencil listing of bookmarks used in the Word document, along with their corresponding fields from the customer database. This helps when writing the code so you not only remember to include all the bookmarks, but spell them correctly. Notice that not all replacements have to be simple replacements of single fields. You can use calculated fields when defining the replacement value for a bookmark. The only restriction is that you must pass a string to the WordBasic command, even if the displayed value is a number or date. Table 1 shows this information for our example.

Table 1: BOOKMARKS AND THE ASSOCIATED TABLE FIELDS—Each bookmark corresponds to one substitution in our mail merge process.

Finally, write the code to read the table, and replace the bookmarks with the appropriate fields from the table:

CLEAR ALL

* Open the client database. 
  IF !USED("CUSTLIST")
    USE CUSTLIST.DBF 
  ELSE 
    SELECT CUSTLIST 
  ENDIF 
  GOTO TOP

* Open a Word Object.
  WordObj = CREATEOBJECT("Word.Basic")

* Open a empty document window first. 
  WordObj.FileNew

* Next, open the original document in a second window. 
  WordObj.FileOpen(CURDIR()+"OLEDemo.DOC")

* Retrieve the number of bookmarks defined in DOC. 
  pnCountMarks = WordObj.CountBookMarks

* Store bookmarks in an array. 
  DIMENSION aMark[pnCountMarks] 
  FOR pnCount = 1 to pnCountMarks 
   aMark[pnCount] = WordObj.BookMarkName(pnCount) 
  ENDFOR

* Close the document template before beginning loop. 
  WordObj.DocClose 

* Begin loop through CLIENT and select records where
* field lSendLtr = .T. 
  SCAN FOR lSendLtr

* Open a copy of the document in an edit window. 
    WordObj.FileOpen(CURDIR()+"OLEDemo.DOC") 

    pcFullName = PROPER(ALLTRIM(cFirstName)) + " " + ; 
                 PROPER(ALLTRIM(cLastName))

* Loop through all the bookmarks in document. 
    FOR pnCount = 1 TO pnCountMarks 
      pcMarkName = UPPER(aMark[pnCount]) 

    * Move to the next bookmark 
      WordObj.EditGoto(pcMarkName) 

      DO CASE 
        CASE pcMarkName = "FULLNAME" 
           WordObj.Insert(pcFullName) 
        CASE pcMarkName = "COMPANY" 
           WordObj.Insert(ALLTRIM(cCompany)) 
        CASE pcMarkName = "ADDRESSLINE1" 
           WordObj.Insert(ALLTRIM(cAddressLine1)) 
        CASE pcMarkName = "ADDRESSLINE2"
           IF EMPTY(ALLTRIM(cAddressLine2)) 
             WordObj.EditCut 
           ELSE
             WordObj.Insert(ALLTRIM(cAddressLine2))
           ENDIF 
        CASE pcMarkName = "CITY" 
           WordObj.Insert(ALLTRIM(cCity)) 
        CASE pcMarkName = "STATE" 
           WordObj.Insert(ALLTRIM(cState)) 
        CASE pcMarkName = "ZIP" 
           WordObj.Insert(ALLTRIM(cZip)) 
        CASE pcMarkName = "FIRSTNAME" 
           WordObj.Insert(ALLTRIM(cFirstName)) 
     ENDCASE 

ENDFOR

* Copy modified document to end of master document. 
    WordObj.StartOfDocument 
    WordObj.EndOfDocument(1) 
    WordObj.EditCopy WordObj.DocClose(2) 
    WordObj.EditPaste WordObj.InsertPageBreak

* Add code here to append other objects to each letter 
* such as the VFP report described later. 
  ENDSCAN

* Print the document. 
  * WordObj.FilePrint 
    WordObj.FileSaveAs(CURDIR()+"DEMO1.DOC")

* Close Word. 
    WordObj.DocClose 
    RELEASE WordObj 

  * Close all tables. 
    CLOSE TABLES

***End of Program

The program opens a Word object with the CREATEOBJECT() command. Note the similarity to creating objects from VFP's base classes.

To create the letter, the program opens a new document in the main document window. Add each letter to this document as it's completed. The document is built from the base letter in a second document window. After inserting the data, it's appended to the end of the document in the main window. Although the program edits the base document in this second window, it doesn't save the changed document back to disk. Therefore, the base document can be either a document file (.DOC) or template (.DOT). The resulting main window document is sent to the printer as a single print job, or saved to a single file.

The program uses the CountBookMarks method to return the number of bookmarks in the document. This value serves as a counter to loop through all bookmarks in the base letter. In fact, the program adds the bookmarks to an array before replacing them. There are several reasons for this. First, as the program replaces each bookmark with text, the bookmark reference is lost in that document instance, which also reduces the CountBookMarks value. Thus, if you attempt to directly use CountBookMarks along with the Word function BookMarkName$(count), the program won't find all the bookmarks. Secondly, by using an array to store the bookmarks one time, you don't have to query Word with each instance of the letter to obtain the bookmark names, since they're the same for all letters.

Next, cycle through the records in the customer file (CUSTLIST.DBF). In this example, the field lSendLtr indicates which customers have previously been identified to receive a letter. (If an index exists on the field lSendLtr, Rushmore optimizes the loop.)

The code in the main loop can be written several ways, depending on the data inserted into the document. If all the bookmarks represent direct replacement of field values into the bookmark, a simple macro substitution (shown below) is used in place of the CASE statement. This technique, which simulates a basic mail merge, locates the bookmark in the document and replaces it with the appropriate data from the table using the Insert method:
PcSubText = EVAL(pcMarkName)
WordObj.Insert(ALLTRIM(pcSubText))

However, to add flexibility with OLE Automation, the main program example uses a CASE statement to allow each insertion to be customized. While all the fields in this example are character type, you can also insert numeric, logical, date, and other field types into the document—just convert them to character strings first. For example, use DTOC() or DTOS() for dates, STR() for numbers, and an expression involving IIF() for logical fields. [Editor's Note: Another approach to this problem is to use a VFP table to hold the bookmark names and the expressions to be substituted. Then, you simply loop through this table.]

Notice that the code used to insert the second address line is different from that used for other bookmarks. That's because not everyone has a second address line. If the second address field is empty, remove the line—don't just leave it blank. In this case, by including the paragraph mark in the bookmark definition, the program cuts the bookmark using the EditCut method, and removes the bookmark and the line. Otherwise, it inserts the address at the bookmark.

After looping through all the bookmarks, the program selects the entire document by moving the insertion point to the start of the document. Then it moves to the end of the document with selection turned on (a non-zero parameter). The EditCopy method places the selected text on the clipboard. Finally, this second document is closed without saving the changes (the parameter 2). This automatically sends you back to the main document window, where you can paste the contents of the clipboard.

The insertion point is always at the end of copied text when pasting documents, which also happens to be the end of the main document. Add a page break to ensure the next document added begins on a new page. The program then continues to loop for other records in CUSTLIST.

After this, you'll probably want to do something with the resulting document. The two obvious choices include printing it or saving it to a file. Both options are shown.To complete the program, close the main document window. A simple call to DocClose works here, because you just saved the document. If you don't want to save the document, use DocClose(2).

While this example was written to run as a stand-alone program, you can easily make it a procedure and call it from a menu option. With a few changes, you can call a modified version of it from a button in a form which currently displays the customer to whom you want to send the letter. In the latter case, you might accumulate letters in the main document window, until the user selects a Print command button or exits the customer form. The possibilities are limitless.

Adding a report
So far, most of this example can be accomplished with a mail merge file. But perhaps you don't want to send just a letter with a few embedded fields. Suppose that you also want to include a report generated from VFP's report writer with each letter. Unfortunately, VFP doesn't generate a Word .DOC or .RTF (Rich Text Format) file as a report output option.

You can, however, output a generic report with no special formatting, and save the report to a file if you include the ASCII clause at the end of a REPORT FORM command. Then you can bring the file into Word and perform limited custom formatting.

Your first step is to bring the report output into a Word document window where you can work on it. The following code opens the report in a separate Word document window. (In the main example, this code would appear immediately prior to the ENDSCAN statement that closes the loop that reads CUSTLIST.)

* Open the saved report file. 
  WordObj.FileOpen(CURDIR()+"RPTTEST.TXT") 
  WordObj.EditGoto('\Doc') 
  WordObj.Font("Courier New", 10)

* Edit the report title. 
  WordObj.EditFind("Warp Drive Test Statistics") 
  WordObj.EditGoto('\Para') 
  WordObj.Font("Arial",20) 
  WordObj.Underline(1) 
  WordObj.Bold(1)

* Edit the header line on each page. 
  WordObj.StartOfDocument 
  WordObj.EditFind("First Name") 
  DO WHILE (WordObj.EditFindFound()=-1) 
    WordObj.EditGoto('\Para') 
    WordObj.Font("Arial", 14) 
    WordObj.Underline(1) 
    WordObj.Bold(1) 
    WordObj.EndOfLine 
    WordObj.EditFind("First Name") 
  ENDDO

* Append the report document to the main document. 
  WordObj.EditGoto('\Doc') 
  WordObj.EditCopy 
  WordObj.DocClose(2) 
  WordObj.InsertPageBreak 
  WordObj.EditPaste

This code selects the entire document and converts it to Courier New (a True Type version of the non-proportional Courier font). The EditGoto line in the code uses a predefined bookmark to select the entire document. This is equivalent to using the methods StartOfDocument and EndOfDocument, and much easier—it only requires a single statement. WordBasic supports several predefined bookmarks (shown in Table 2). These aren't listed in the bookmark list opened through the Edit menu.

Suppose you know the report has a title containing the text, Warp Drive Test Statistics. You can search for this string, and use methods in Word to change its format. Change the title to Arial, 20 point, bold, underlined, and centered. (Note that, after locating the search text string, the program first selects the entire line before applying the formatting.)

Knowing that you can format a title line, it's a simple leap to format the header on each page. The main difference is you must find not just the first occurrence of a text string, but each subsequent occurrence as well. This is accomplished with a loop that checks whether the string has been found using the EditFindFound() method. If EditFind is successful, it returns a value of -1. The code locates lines that contain the text, First Name, and formats those lines in Arial 14 point, underlined, and bold.

Finally, the code moves this report to the end of the main document. It accomplishes this the same way you copy the modified letters to the main document window.

You can even enhance this document further by adding an Excel chart created from data stored in a FoxPro table.

As powerful as this technique is, you can't use it with every Windows application—at least not yet. First, the application you want to control must support OLE 2. Second, it must also support OLE Automation—an optional component of OLE 2 technology. For now, two primary candidates for OLE Automation control are Microsoft Word and Excel.

Table 2: PREDEFINED WORD BASIC BOOKMARKS—Use these in any document to find your way.

However, other OLE 2-compliant applications may support OLE Automation. Check the manuals that come with the product, or check with the manufacturer of your software to find out for sure. (And if it doesn't support OLE Automation, ask when it will.) Until then, try some of these new tools with Word or Excel. Visual FoxPro is great by itself, but the combination of Visual FoxPro with other OLE Automation servers is even more awesome.

Michael P. Antonovich is director of training for Documentation, a Microsoft Solution Provider specializing in database consulting and training located in Orlando, Florida. He's also the lead author of Using Visual FoxPro, Special Edition (Que) and has authored two other FoxPro books. 70244.1167@compuserve.com.