Instead of forcing FoxPro to be all things to all people, use Object Linking and Embedding to get the best out of several different applications.

By Miriam Liskin, Contributing Editor

I've been saying for years that when it comes to software (and software companies' promotional T-shirts), one size doesn't always fit all. That's why a couple of big guys I know are wearing most of my FoxPro T-shirts, and that's also why I don't try to use Visual FoxPro as a word processor or spreadsheet.

In the old days, when you used different programs to do tasks for which they were best suited, you had to import, export, and convert data to the required file formats. Importing and exporting still play a role in some kinds of data exchange, but you can now use Object Linking and Embedding (OLE) to integrate foreign objects into a Visual FoxPro application.

OLE lets you incorporate objects (such as documents, spreadsheets, sound recordings, and video clips) into a VFP application by storing the objects in General fields in tables, or by defining form objects to access them. Yes, you can do all kinds of cool multimedia stuff with OLE, but this column won't tell you how to play music or show movies; I'll stick with more prosaic examples based on Microsoft Word and Excel.

Visual FoxPro knows nothing about how to display, edit, or run OLE objects, and it doesn't have to. In fact, it doesn't even have to know which OLE server applications exist or which ones are installed on your computer; such information is stored in the OLE object itself and the Windows Registration database. When you (or the users of your application) need to display or edit an OLE object, Visual FoxPro invokes the server application that created it, and this application does all the work.

Philosophically, this approach works much like client-server database software, and you'll see the same terminology: The application that created an object is called the OLE server, and the application using the object (and calling up the server to display or edit it) is called the client.

The bad news is that using OLE objects makes an application less portable. If the server application is missing when you try to display or edit an OLE object, you receive an error message. For example, if you embed Microsoft Word files in a Visual FoxPro table, then give your application to colleagues who use WordPerfect, they won't be able to read the document.

Using general fields to store OLE objects
As I mentioned above, you can store OLE objects in General fields in a Visual FoxPro table. For example, in a client tracking system, you can put a copy of the contract document in the client table; in a project-management application, you can store the project budget spreadsheet in the project table. In a personnel file, you can store the employees' photographs and signatures in the employee table.

As the acronym suggests, OLE lets you create a link (a reference) to the original object, or embed a copy in your Visual FoxPro table. The deciding factors are transportability and whether you need to update the OLE objects outside your Visual FoxPro application. When you create a new embedded object, the object may never exist as an independent disk file. If you do embed an existing file, you're storing a copy. If you (or another user) edits the original outside your Visual FoxPro application, the changes won't be reflected in the copy stored in the table.

If your Visual FoxPro users need to modify or see the latest copy of documents that are also updated outside your Visual FoxPro application (possibly by other users), you should choose linking whenever possible. If you're working with documents that are local to your Visual FoxPro application, embedding lets you easily back up or distribute the application, together with all its OLE data.

Unfortunately, the decision isn't always yours to make, because some OLE server applications don't support linking. In particular, applets such as Microsoft Graph (which comes with Visual FoxPro and many other Microsoft applications) and WordArt (which comes with Microsoft Word) are always called from another application and can't create new disk files; therefore, such applications don't let you create linked objects. You'll also encounter some standalone programs that don't support linking, even if in theory they could.

Surprisingly, storage space isn't an issue. You won't save much space by linking rather than embedding; the "link" usually takes almost as much space as the entire OLE object would if you embedded it.

Visual FoxPro exerts virtually no control over the kind of data you put in a General field. Working interactively, you can store different kinds of OLE objects in different records in the same table, and link some while embedding others. When using forms to update tables, you can take some measures to encourage consistency. I'll demonstrate how to do that later in this article.

Working interactively
If you haven't done much with OLE, it's a good idea to experiment a bit from the Command window before you begin designing forms. These interactive techniques will also serve you well for troubleshooting OLE problems. If something doesn't work in a form, try opening the table in Browse and see if you can accomplish the same task interactively.

As is the case with Memo fields, the data in General fields isn't visible in the default Browse or Edit screen. Visual FoxPro displays the field as "gen" if it's empty, or "Gen" if it contains data. To view or edit the contents, tab into the General field and press Ctrl+Home, or double-click in the field. These commands open an editing window like the one shown in figure 1. Usually, you'll want to leave this window open, so you can always see the contents of the General field in the current record as you move through the table. OLE objects that have no meaningful static representation (such as sound or video recordings) are displayed as the standard Windows icon for the server program.

All the options for creating and manipulating OLE objects are on the Edit menu. If the General field that has the focus is empty, only Insert Object is enabled; choosing this option calls up the dialog shown in figure 2, which lets you embed an OLE object. (You can't create a linked object this way; you have to use the Windows Clipboard, as I'll explain shortly.)

Figure 1: DISPLAYING GENERAL FIELDS—You can display the contents of a General field in Browse or Edit.

Figure 2: CREATING A NEW OLE OBJECT—You can create a new OLE object and embed it in a General field.

Creating a new object
If you want to create a brand-new OLE object, you must identify its type (and, indirectly, the server application) by choosing it from the scrolling list. This list comes from the Windows Registration database, which stores the associations between applications and the file types they create, so your list won't exactly match the one in figure 2; you'll see only object types supported by OLE server applications installed on your system. When you choose an object type and click on OK, Visual FoxPro immediately opens the server application to create the object.

What happens next depends on whether the OLE server application supports in-place activation (or, as Visual FoxPro calls it, visual editing). If so, you'll be working within the boundaries of the window you opened to view the General field, using a menu bar and toolbars that contain some Visual FoxPro commands and other commands derived from the server application. To save the embedded object and close the server application, simply click anywhere outside the General field window.

If you're primarily a Visual FoxPro user, you might view in-place activation as more intuitive, in that you never leave the familiar Visual FoxPro environment. However, if you're equally conversant with Word or Excel, you might find it unnerving not to see the full menu bar and toolbars normally displayed by these programs.

If the server application doesn't support in-place activation, it opens in its own window—exactly as if you had launched it independent of Visual FoxPro. In this case, you'll find an Update option on the File menu in place of the usual Save option, and the Exit command will be worded "Exit and Return to Visual FoxPro."

Adding an existing OLE object
To embed a copy or create a link to an existing OLE object, choose Create from File in the Insert Object dialog (figure 2). If you're working with an existing file, you don't have to identify its type; this information is stored in the Registration Database. In this case, use the Open File dialog (figure 3), which lets you type the name of the file or use the Browse button to search for it. If you want to create a link rather than an embedded object, select the Link check box. In this scenario, Visual FoxPro doesn't assume you want to edit the object, so it doesn't automatically open the server application.

Figure 3: USING AN EXISTING OLE OBJECT—You can embed, or create a link to, an existing file.

Editing an OLE object
To edit an OLE object already stored in a General field, simply double-click in the editing window to activate the server application in place, or in its own window if it doesn't support in-place activation. If you prefer to choose which way the server application opens, use the Object option on the Edit menu instead. (The exact wording of this menu option depends on the object type; you might see Document Object, Worksheet Object, Paradox 5 Object, among many others.) The Object option displays a submenu with two choices, as shown in figure 4. Open always opens the application in its own window, while Edit opens it in place if the server application supports in-place activation.

Using the clipboard
You can also use a simple cut-and-paste technique to embed an OLE object in a General field. To use this method, open the server application, create or select the data you want to place in the General field, return to Visual FoxPro, and use the Paste or Paste Special commands on the Edit menu to insert the data. The Paste command (or the equivalent hot key, Ctrl+V) embeds the selected data in the General field. The Paste Special command calls up the dialog shown in figure 5 to let you choose the format of the data and, if you want, specify that you want the object linked rather than embedded. (In most cases, you'll retain the default format.)

Figure 4: EDITING AN OLE OBJECT—You can choose whether you want to open the OLE server application in place or in its own program window.

Figure 5: PASTING AN OLE OBJECT—You can copy all or part of an OLE object to the Clipboard and use the Edit | Paste Special command to embed or link the data in a General field.

Building forms
OK, I know you're ready to close that Browse window and start building a form. However, all the foregoing material wasn't just a learning exercise in interactive FoxPro. If you're willing to use the same commands to create and edit OLE objects in forms, building the forms is trivial: Simply create a bound OLE control to display the General field that stores the OLE data.

You can place unbound OLE controls—objects not stored in a table, such as graphs or pictures—on a Visual FoxPro form, but you use the unbound OLE Container tool to do so.

NOTE: The unbound OLE Container and Bound OLE Control buttons in the toolbar look quite similar, so watch for the ToolTip if you're not sure which is which.

There are just a few properties you need to set. In particular, the AutoActivate property determines how the user activates the object. The settings have the effects shown in table 1.

Table 1: AUTOACTIVE PROPERTY SETTINGS—the AutoActIvate property determines how the user activates the object.

If the OLE server application supports in-place activation, the 1 - GotFocus setting makes the OLE bound control behave just like any other control on the form. The user can simply tab into it and begin editing. Another good choice (better for keyboard users who prefer to tab through the controls on a form in sequence) is the default setting, DoubleClick.

Be aware that you can't set the foreground or background colors for an OLE bound control (these properties are derived from the object itself), and you can't display a visible border around the object. The easiest way to simulate these effects is to place the OLE bound control on top of a rectangle object of the desired color, with the desired border style.

Taking the reins
When designing forms for users who aren't familiar with Visual FoxPro or OLE, you might prefer to exert more stringent control over the process of inserting and editing OLE objects. You might also hope to restrict what type of OLE object users can place in a field. You can't, as you might have hoped, use the OLEClass property for this purpose. Although you can query this property from a method to determine the actual OLE class of an object (the class name assigned to the server application that created the object), you can't set it in the Form Designer to force the control to accept only a particular class of OLE object.

Figure 6 shows a form called MLMAIL9, which demonstrates some techniques for manipulating OLE objects in code. This form updates a new version of the Maillist table from the sample mailing list application used throughout this series, which includes a General field called Contract.

There are two basic techniques for manipulating OLE objects in code:

• Use the APPEND GENERAL command to insert an OLE object into the field.
• Use the DoVerb method for the OLE bound control to edit the object.

You can use the APPEND GENERAL command from the Command window or from any Visual FoxPro program, so you can try some simple examples without building a form. This basic format of this command is:
APPEND GENERAL <field name> FROM <file name>

By default, the APPEND GENERAL command creates an embedded object; to create a link instead, add the keyword LINK to the command.

Figure 6: EDITING OLE OBJECTS IN A FORM—You can update OLE objects in forms by using OLE bound controls.

If you're not sure whether the file extension is sufficient to identify the OLE server application, you can add a CLASS clause, like this:

APPEND GENERAL Contract ; 
       FROM C:\WORD6\DOCUMENT\STDCONTR.DOC ; 
       CLASS Word.Document.6

You can create a placeholder for a specific type of OLE object by specifying only the class, without including a file name:
APPEND GENERAL Contract CLASS Word.Document.6

If you execute this command each time you add a blank record to the table, double-clicking an OLE bound control based on the Contract field automatically calls up Word. However, it won't prevent the user from deleting the Word document and putting a spreadsheet or graphic image in its place.

If you want to ensure that users can only manipulate OLE objects using command buttons on your form, you can remove the object commands from the Edit menu (if you're using a custom menu bar for your application) and set the AutoActivate property for the OLE bound control to 0 - Manual. You must then use the DoVerb method to activate the object.

The DoVerb method takes one numeric parameter, which specifies which of several activation methods you want to use. The ones you'll use most often are shown in table 2.

Table 2: DOVERB METHOD PARAMETERS—Specifies the activation method you want to use.

For example, you can open the server application for the oleContract object with:
THISFORM.oleContract.DoVerb(-1)

The form in figure 6 uses this command in the Click method for the Edit button.

The Delete button uses the BLANK FIELD command to empty the Contract field, then disable the Edit button (because there's nothing to edit). If you haven't encountered the BLANK FIELD command before, note that this command can be used for any type of field (not just General fields), but it's the only way to delete the contents of a General field. Here's the complete command sequence:
BLANK FIELD Contract
THISFORM.oleContract.Refresh()
THISFORM.cmdEdit.Enabled = .F.

Finally, the New button lets the user insert an existing document into the Contract field or create a new document. The best way to encourage the user to do the right thing is to display an Open File dialog that includes only files of the specified type—in this case, Word documents:

LOCAL lcFileName
lcFileName = GETFILE("DOC", "Choose a document:", ; 
                      "Open", 1)
IF EMPTY(lcFileName) 
   RETURN
ENDIF
IF UPPER(RIGHT(lcFileName, 3)) <> "DOC" 
   =MESSAGEBOX("You must choose a Word document file", ; 
              MSGOKONLY + MSGEXCLAMATION, C_APPNAME) 
   RETURN
ENDIF
IF "UNTITLED" $ UPPER(lcFileName) 
   APPEND GENERAL Maillist.Contract CLASS Word.Document.6
ELSE 
   APPEND GENERAL Maillist.Contract FROM (lcFileName)
ENDIF
THISFORM.cmdEdit.Enabled = .T.
THISFORM.oleContract.Refresh()
THISFORM.oleContract.DoVerb(-1)

This routine calls the GETFILE() function to display the Open File dialog like the one shown in figure 7, which includes only files with the extension .DOC. (If you're not familiar with this function, I covered it in the February 1995 issue.) GETFILE() returns the name of the file selected from the dialog, or an empty string if the user exits by clicking on the Cancel button.

The Edit routine performs some basic error-checking on the file selected by the user. If the extension isn't .DOC, it displays an error message and returns the user to the form to try again. This isn't an entirely satisfactory solution, because a Word file could have an extension other than .DOC, and files created by other programs could have this extension. You might prefer to simply ask the user for confirmation, rather than rejecting the selected file unequivocally.

Figure 7: CHOOSING A FILE—You can use the GETFILE() function to let the user open a file of a specified type.

If the user chooses New from the Open File dialog, the GETFILE() function returns "UNTITLED" and the method uses an APPEND GENERAL command that specifies only the class for the new object. Otherwise, it appends the contents of the file the user selected.

This version of the New command button Click method assumes the user will want to edit the object immediately after inserting it in the Contract field, so it calls the DoVerb method for this purpose. If you don't agree with my assumption, just take out that method call.

What's next?
There's one more layer of power and complexity: OLE Automation, which lets you write programs that control an OLE server application using its own command language (Word Basic, Visual Basic for Applications, and so on). With this technique, you can harness all the capabilities of the server application behind the scenes, with no user intervention.

For example, you could construct a Word document that performs a mail merge and prints letters and envelopes, all done from a Visual FoxPro program. Of course, this requires some familiarity with the command language of the server application and, nowadays, an understanding of its object model, and that's beyond the scope of this column.

Contributing Editor Miriam Liskin is an Xbase programmer and writer who divides her time between Northern California and Southern Utah. Her most recent books are Miriam Liskin’s Visual FoxPro Expert Solutions (Que), and PC Magazine Programming FoxPro 2.5 (Ziff-Davis Press). 71241.2333@compuserve.com, mliskin@oro.net.

Article reprinted with permission from FoxPro Advisor, Vol.4, No. 8 © 1996 Advisor Publications Inc. 5675 Ruffin Road, San Diego, CA 92123. For more company and subscription information, call (619)278-5600, fax (619)279-0300, Internet advisor@advisor.com.