Updated: March 13,1996

Go To TechNet Home Page

Controlling MS Office Applications with Visual FoxPro 3.0

Microsoft DevCon 95 Speaker Materials

This document is reprinted from the Microsoft DevCon 95 Speaker materials and is provided "as-is." Slides are not included due to space limitations. Demos, if included, are not necessarily fully-functional samples. This document and any associated demo files were created using a beta version of Visual FoxPro 3.0. There may be differences between the beta version used to create these materials and the release version of Visual FoxPro 3.0.

OLE Automation

Introduction

Since the introduction of FoxPro for Windows, developers have enjoyed the ability to embed or link documents from other applications directly into FoxPro tables thanks to OLE technology. While you still have this capability with OLE 2.0, the new specification goes much, much further than that. In fact, Microsoft has publicly stated that the future of all Windows based products, as well as future versions of Windows itself, will be based on this technology. This session will focus on OLE Automation, showing you how to use it to incorporate the power built into other Microsoft Office products like Word and Excel into your own FoxPro application.

What and Why?

OLE Automation was designed to allow application developers and end users to control applications through a common interface. An application that is OLE Automation enabled allows other external applications to control it by exposing certain parts of its internal structure in the form of objects. These objects have properties and methods. Properties allow you to retrieve values that might represent the state of the object, a count of some number items, another object, etc. Methods allow you to perform an action on a specific object, like opening a new document, printing a file, recalculating a range of cells, etc.

Why would you want to use this functionality? How many of us have had to write sophisticated financial routines when we knew that the same routine was already available in our favorite spreadsheet? How many of us have written scheduling routines when we wished we could somehow hook into our favorite project management software? Through the power of OLE Automation, we now have the capability to hook into other applications and use them as if they were a part of our own application. Imaging having all of Excel's built in functions available at your fingertips. Even better, imagine having Excel's powerful charting capabilities built in to your own application! Imagine no more! All this is now possible and readily accessible through OLE Automation!

Quick Review

There are only two FoxPro functions you need to worry about in order to retrieve another application's objects. Once you have obtained the object, you can immediately begin to access properties and methods of the object.

CREATEOBJECT( )

Creates an object from a class definition or an OLE object. This command is also used to create objects of built in FoxPro or user-defined classes. Since this session is about creating OLE objects, I will intentionally leave out the syntax and descriptions of creating other types of classes to avoid confusion. For consistency with Microsoft documentation, much of this information in this section is derived directly from FoxPro's help file.

Syntax:

<memvar> = CREATEOBJECT(cClassName)

cClassName: Specifies the OLE object from which the new object is created.

FoxPro will search for the class name you specify in the following order:

1. Visual FoxPro base classes.

2. User-defined class definitions in memory in the order they were loaded.

3. Classes in the current program.

4. Classes in .VCX class libraries opened with SET CLASSLIB.

5. Classes in procedure files opened with SET PROCEDURE.

6. Classes in the Visual FoxPro program execution chain.

7. The Windows Registry (for OLE objects).

OLE objects are created using the following syntax for cClassName:

ApplicationName.Class

For example, to create a Microsoft Excel object using OLE automation, you can use the following syntax:

xl = CREATEOBJECT("Excel.Application")

When this code is run, Microsoft Excel is started but is not visible. You will not see Excel if you ALT+TAB through all your running applications, nor will you see it if you bring up the Task Manager with CTRL+ESC. Note that if Excel is already running, another separate instance will be created when you execute this command. Also note that Word functions a little differently, as will be explained in the "FoxPro 3.0 OLE Automation" section.

GETOBJECT()

Activates an OLE automation object and creates a reference to the object.

Syntax:

GETOBJECT([cFileName [, cClassName]])

cFileName: Specifies the full path and name of the file to activate. The application does not need to be specified, because the OLE dynamic link libraries determine the application to start based on the file name you provide.

For example, the following code launches Microsoft Excel, opens a file named BUDGET.XLS, and creates a reference through an object memory variable named xlBudget:

xlBudget = GETOBJECT("C:\EXCEL\WORK\BUDGET.XLS")

cClassName: Specifies the class name of the object to retrieve. Some applications can store more than one object type in the same file, allowing you to use the class name to specify the object to activate. For example, if a word processing application stores its documents, macro definitions, and ToolBar objects in the same file, you can create a reference to the document file with the following command:

mDocFile = GETOBJECT("C:\WRDPROC\MYDOC.DOC", "WrdProc.Document")

If you specify the empty string ("") for cFileName, GETOBJECT( ) creates a reference to the currently active object of the class you include. For example, if Microsoft Excel is running, you can create a reference to it with the following command:

xl = GETOBJECT("", "Excel.Application")

If Excel is not running, a trappable error will be generated. Trapping the error and resorting to the CREATEOBJECT() function is a common technique to test if an application is already running. An alternative technique is to use DDE to establish a link with the server application on the System topic, which all DDE applications support. If the link is successfully established, the application is running. This concept is covered in more detail in the "FoxPro 3.0 OLE Automation" section.

Dealing With Complex Object Models

My first glimpse at Excel's object model was very intimidating. There are over 100 objects available to the OLE Automation developer. Each of these objects has a myriad of properties and methods at your disposal. You don't have to memorize each and every one of these properties and methods to be productive with Excel; however, it helps to at least become familiar with what's there.

A great way to learn how to use these properties and methods is through Visual Basic for Applications (VBA), Excel's built-in macro language. You may be wondering how learning another programming language will help you to understand how to use Excel's object model from FoxPro. The answer is in knowing how VBA communicates with it's host environment, Excel. VBA uses the same objects, properties, and methods that are available to FoxPro through OLE Automation. In fact, VBA itself uses OLE Automation to communicate with Excel. Knowing this, we can use Excel's built in macro recorder to record the functionality that we are trying to model. The macro recorder generates VBA code, which you can examine to see how various objects are being utilized. Granted, not every construct in VBA is directly portable to FoxPro, but VBA interacts with Excel much in the same way that FoxPro does, and the syntax when dealing with Excel objects is almost identical. In many cases you will discover new objects or techniques that you previously didn't know existed.

Another great way to become familiar with an application's objects is through the command window, from which you can interactively create objects, set object properties, call object methods, and just plain have fun with OLE Automation.

Hardware Requirements

All this power does not come for free, of course. Memory is the most important requirement when setting out to write OLE Automation applications. The more memory you have, the better. Determining exactly how much memory you need depends upon the memory requirements of the operating system you're using, as well as the memory requirements of the application you wish to control. For bare minimums, I would recommend the following:

Operating System                        Minimum Memory for OLE  
                                        Automation              

DOS/Windows 3.11 or WFW 3.11 with       16mg                    
WIN32s                                                          
Windows NT Workstation 3.5              24mg                    
Windows 95                              16mg?                   

The Registration Database

The registration database is where FoxPro looks for the class name you specify with both the CREATEOBJECT() and GETOBJECT() functions. Under Windows 3.11 and Windows for WorkGroups 3.11, entries into the registration database are stored as a key and a corresponding value for each key in a tree-like structure. You can view the registration database with a program called REGEDIT.EXE, which is located in the directory where you installed Windows. Running REGEDIT with the /V option, which stands for verbose, will produce the screen you see in Figure 1.

Figure 1-16-bit Registration Database

Under Windows NT and Windows 95, the tree-like structure remains but the entries are grouped into different categories. Look under the HKEY_CLASSES_ROOT category or window to find entries for OLE Automation objects. The Windows NT 3.5 registration database editor was invoked by running the REGEDT32.EXE program with no options. REGEDT32.EXE can be found in the SYSTEM32 sub-directory under the directory where Windows NT was installed. Note that other windows of the registration database are not shown. (Figure 2 contains a view of the editor with the HKEY_CLASSES_ROOT windows maximized).

Figure 2-Windows NT Registration Database

FoxPro 3.0 OLE Automation

Creating a Generic OLE Application Class

One of the greatest advantages of using OLE Automation from within FoxPro is due to the new object oriented extensions. Because you now have the ability to create custom classes, and because classes serve to encapsulate both data and behavior, you can build classes that shield you from having to worry about the peculiarities of a specific type of OLE server application.

To illustrate this, let's look at the differences between controlling Word and Excel by beginning to develop a class that defines how we'll want to control all of our OLE applications. We will not design this class to be instantiated directly. (Classes that are designed in this fashion are called abstract classes). Rather, we will create a subclass of this class for each specific OLE application that we want to control.

#INCLUDE "\VFP\FOXPRO.H"

DEFINE CLASS OLEApplication AS Custom

*-- oOLEApp - Holds reference to our OLE Application Server

*-- cOLERegName - The name of the server object as found in

*-- the registration database.

*-- lCloseAppWhenDone - .T. if we started the server for the

*-- time within this class

PROTECTED oOLEApp, ;

cOLERegName, ;

lCloseAppWhenDone

oOLEApp = ""

cOLERegName = ""

lCloseAppWhenDone = .T.

*-- Methods

FUNCTION Init()

*-- First make sure that the user is not trying to create an

*-- instance of this class.

IF EMPTY(this.cOLERegName)

=MessageBox("Cannot create object directly from class OLEApplication", ;

MB_ICONSTOP, ;

"")

RETURN .F.

ENDIF

*-- Attempt to start the application

*-- First, check to see if app is already running

IF this.AppRunning()

*-- Grab the current instance

this.oOLEApp = this.GetCurrentInstance()

ELSE

*-- Create a new instance.

this.oOLEApp = this.CreateNewInstance()

ENDIF

ENDFUNC

*-- Additional methods with are described below get

*-- inserted here.

ENDDEFINE

This defines the core functionality of our abstract OLE Application class. We first check to make sure that the user of the class is not trying to instantiate it directly. It they are, we just cancel the INIT method, which prevents the object from being created. Secondly, we check to see if the app is running. If it is, we create a reference to it. If not, we create a new instance.

You may be wondering why you need to determine if the application you're trying to control is currently running. The reason is that certain applications let you create multiple instances, while others do not. If you were to execute the following three commands from the command window, you would wind up with three separate running instances of Excel:

xl1 = CREATEOBJECT("Excel.Application")

xl2 = CREATEOBJECT("Excel.Application")

xl3 = CREATEOBJECT("Excel.Application")

If you try this with Word, you'll wind up with just one instance, and three references to the same instance. So how do we determine if an application is already running? Here are two suggestions.

The first way involves use of the GETOBJECT() function to attempt to retrieve a reference to an OLE Automation server. At the time of this writing, this option works fine with Excel, but not with Word. We'll override this function when we define our WinWord class, but for now, let's define this option as a protected function within our OLEApplication class. (We define the function as protected because we will only be calling this function from within the OLEApplication class or any class that we derive from it. There is no need to expose it to the "outside world").

PROTECTED FUNCTION AppRunning()

*-- Returns .T. if app is already running

LOCAL lcOldError, ;

llRunning

llRunning = .T.

lcOldError = ON("ERROR")

ON ERROR llRunning = .F.

*-- Attempt to get a reference to a running application

=GETOBJECT("", this.cOLERegName)

ON ERROR &lcOldError

this.lCloseAppWhenDone = !llRunning

RETURN llRunning

ENDFUNC

The second way of determining if an application is already running involves the use of DDE. So as not to disrupt the definition we are building of the OLEApplication class, I will defer discussing this alternative method until we create our WinWord class, a subclass of the OLEApplication class.

To complete the definition of the OLEApplication class, we create two methods, also defined as protected functions, that we will use to create references to new instances or retrieve references to current instances. You'll notice that each method contains only one statement. We could have eliminated the overhead of the function call and placed the CREATEOBJECT() and GETOBJECT() calls directly in the Init method, but, if we needed to, we would not be able to customize the behavior of these methods in our subclasses. An example of customizing the behavior of these methods can be found in the GetCurrentInstance() method of the WinWord class. (See below)

PROTECTED FUNCTION CreateNewInstance()

RETURN CREATEOBJECT(this.cOLERegName)

ENDFUNC

PROTECTED FUNCTION GetCurrentInstance()

RETURN GETOBJECT("", this.cOLERegName)

ENDFUNC

The Excel Class

Let's define our first subclass to handle OLE Automation with Excel. Most of the inherited functionality from the OLEApplication class works just fine with Excel. The first thing we need to do is initialize the cOLERegName property with the appropriate name of the Excel Application object as it appears in the registration database. Secondly, we need to setup a Destroy event method that sends the Quit command to Excel whenever the object is being destroyed. The Destroy event method will fire whenever we explicitly release an Excel object, or whenever the Excel object goes out of scope.

Note that we do not tell Excel to quit if the internal flag, lCloseAppWhenDone, is set. This flag will be .F. if Excel was already running when we created an instance of this class. By providing this functionality, we are making Excel work more closely resemble Word, where the application is closed when the reference to it is released from memory.

DEFINE CLASS Excel AS OLEApplication

*-- Inherited properties

cOLERegName = "Excel.Application"

*-- Methods

FUNCTION Destroy()

IF TYPE("this.oOLEApp") == "O" AND ;

this.lCloseAppWhenDone

this.oOLEApp.Quit()

ENDIF

ENDFUNC

ENDDEFINE

The WinWord Class

Our second subclass deals with intricacies of dealing with Word. Not that Word is any more difficult to deal with than Excel, it just responds differently in different ways. I mentioned earlier the GETOBJECT() function does not work properly with Word to check if an instance is running. We therefore turn to DDE to attempt to establish a link with Word. If we are successful, we know that Word is running, so we immediately terminate the link.

DEFINE CLASS WinWord AS OLEApplication

*-- Inherited properties

cOLERegName = "Word.Basic"

*-- Methods

FUNCTION Init()

IF !OLEApplication::Init()

RETURN .F.

ENDIF

this.oOLEApp.AppMinimize()

ENDFUNC

PROTECTED FUNCTION AppRunning()

LOCAL lnChannel, ;

llRunning, ;

llDDEOldSafety

llDDEOldSafety = DDESETOPTION("Safety")

*-- Prevent the prompt to start the application

=DDESETOPTION("Safety", .F.)

*-- Try to establish a link on the System topic

lnChannel = DDEINITIATE("WinWord", "System")

IF lnChannel <> -1

*-- It's running

this.lCloseAppWhenDone = .F.

=DDETERMINATE(lnChannel)

llRunning = .T.

ENDIF

=DDESETOPTION("Safety", llDDEOldSafety)

RETURN llRunning

PROTECTED FUNCTION GetCurrentInstance()

RETURN CREATEOBJECT(this.cOLERegName)

ENDFUNC

ENDDEFINE

Notice that we have also defined a new implementation for the GetCurrentInstance() method. This is because of Word's inability to respond to the GETOBJECT() method. To get the current instance of Word, we can safely use the CreateObject() method.

It is interesting to note that Word does not support creating multiple instances of itself through OLE Automation, while Excel does. The use of the classes presented here will prevent you from having to worry about inadvertently creating another instance of Excel.

Another interesting difference between these two applications is that when releasing a reference to Word that was started via OLE Automation, the Word instance is automatically terminated. However, a reference to Excel will not terminate in this fashion. Instead, we must sent the Quit method to the Excel object, explicitly instructing it to terminate. Furthermore, Excel will terminate even if the reference to it was created from a previously running instance.

More Ideas

Although you could if you wanted to, you probably won't want to create custom methods for every single method of every single object in an OLE server application. Instead, you may want to provide a quick way to indirectly access the properties and methods of the application object itself, without removing it's protected status. If we were to simply provide direct access to the application instance, we would loose control over it. The user would then have the ability to disrupt the environment by incorrectly setting properties or calling methods that we may not want them to call. Instead, we can keep the application instance as a protected member of our class, and provide three methods that provide indirect access to the internal application instance.

The first two methods are implemented the same way. They are intentionally left as two separate methods because they serve two different purposes, and also to make it easier to subclass in the future.

*-- This method takes a method name as a parameter

*-- and executes it.

FUNCTION Do(tcMethod)

RETURN EVAL("this.oOLEApp." + tcMethod)

ENDFUNC

*-- This method takes a property name as a parameter

*-- and returns its value. (It can even return references

*-- to container objects).

FUNCTION Get(tcProperty)

RETURN EVAL("this.oOLEApp." + tcProperty)

ENDFUNC

*-- This method takes a property name and a value as

*-- parameters, and sets the value of the property

*-- to the value parameter.

FUNCTION Set(tcProperty, tuValue)

LOCAL lcCommand

lcCommand = "this.oOLEApp." + tcProperty + "="

lcCommand = lcCommand + this.ConvertToChar(tuValue)

&lcCommand

ENDFUNC

We'll add just one more method here to convert a generic parameter of any type to type character. This method could just as easily have been implemented as a standalone function in it's own PRG, or as a function of a procedure file. For now, it remains a protected method of this class.

*-- Takes a parameter of any type and converts it

*-- a character string for use in the Set method.

PROTECTED FUNCTION ConvertToChar(tuParam)

LOCAL lcRetVal, ;

lcType

lcRetVal = ""

lcType = TYPE("tuParam")

DO CASE

CASE lcType = "C"

lcRetVal = "'" + tuParam + "'"

CASE INLIST(lcType, "N", "B")

lcRetVal = STR(tuParam)

CASE lcType = "L"

lcRetVal = IIF(tuParam, ".T.", ".F.")

ENDCASE

RETURN lcRetVal

ENDFUNC

And that's all there is to it. Through these methods, we provide access to the internal application instance through a custom interface to the class. We could implement a set of rules governing which methods we want the user to be able to call, thereby protecting the user of the class from doing anything they aren't supposed to. If we desire, we could ultimately remove these three methods (actually four if you include the ConvertToChar utility method), and add custom methods that perform specific tasks we want to accomplish. You don't have to restrict yourself by mapping each and every method of each and every object. Rather, you could create higher level methods which perform higher level tasks, which shield the user of the class from having to know the details of the OLE Automation commands that are required to perform the desired functionality. We could do this gradually as application requirements change, while still retaining the core functionality we have here.

OOP and OLE Automation-perfect together.

Return to the Top

Click Here to Search TechNet Web Contents

TechNet CD Overview

Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information.

Go To TechNet Home Page

©1996 Microsoft Corporation

Go To Microsoft Home Page