OLE From a Developer's Perspective

The OLE specification includes a feature called OLE automation, which lets developers do three things:

• Control, through code, objects exposed by another application

• Control, through code, custom business objects that are OLE compliant

• Control, through code, linked and embedded objects

Applications that can control another application's exposed objects are called OLE automation clients, and applications that expose objects are called OLE automation servers.

Although you need Visual Basic 4—or, for greater control and speed, C/C++—to create objects, you can use the objects that you create (or that are exposed by shrink-wrapped products) with any programming language that provides "client-support" for OLE automation. Exposed objects are considered to be objects only by a programming language that can manipulate them. For example, to a language that includes OLE automation capabilities, WordBasic is an object, the Microsoft Excel application itself is an object, and so are Microsoft Excel worksheets. People who use integrated business applications, however, may never know that another application is involved behind the scene. The following table shows the extent to which the Microsoft BASICs support OLE automation

How the Microsoft BASICs Implement OLE

Where Am I Now?

One issue that comes up in the design of integrated applications is how to organize the application. There are three main organizational models:

• The app-centric model, in which one Office application controls the others. BulkMail, a sample application presented later, uses this model. This application is written in VBA for Microsoft Excel and uses OLE automation to control Word.

• The .EXE model, in which all component Office applications are controlled from a stand-alone .EXE file, such as one built in Visual Basic.

• The to-each-its-own model, in which each Office application and/or .EXE file does its share of the processing. A sample application presented later, PIVOT, uses this model, which requires that you write code in the component applications as well as in the startup application. (Another sample application presented in Chapter 12 of Developing Applications with Microsoft Office, PublshXL, uses this model and DDE from Word to control Microsoft Excel.) This model is useful when you can't control processing in component applications through OLE automation. This might be because the client application doesn't support OLE automation (which is the case in the sample application PublshXL), or because the client application doesn't support a particular data type or method used by the server application (as in the sample application PIVOT). This model also increases efficiency. For example, VBA for Microsoft Excel code always runs faster from within Microsoft Excel than from a remote application via OLE automation.

Who's in Control?

Another issue that comes up when you design integrated applications is how much users should see of component applications. Here are a few rules of thumb, which depend on the nature of the integrated business application you're developing:

• When you develop integrated applications that use a particular Office product to deliver data to users, you generally should leave users in that product. For example, if you build an Access application that generates a pivot table or a report in Microsoft Excel, leave users in Microsoft Excel.

• When you develop integrated applications that use only the functionality of a shrink-wrapped product, users should never see that product. For example, if you build an application in Access, Word, or Visual Basic that uses Microsoft Excel to perform a series of calculations and return a number, users don't need to (and shouldn't) see Microsoft Excel. This model improves both the speed of integrated applications and their integrity because it prevents users from interfering with the application's execution.

• Unless you're automating the process of building compound documents that require users to edit or update the source data, there's no reason to link or embed data from one application into another.

• When you develop integrated applications that take data from one application and integrate it into another, try to do it in such a way that you don't have to launch another shrink-wrapped product. This provides the best performance. For example, don't copy data from Access to Microsoft Excel or Word; instead, use ODBC. Launching Office applications is one of the most time-consuming things you can do, so it's worthwhile to explore alternatives.

Minimizing Problems for Users

Whenever you build applications that open more than one Office product simultaneously, you can minimize the possibility that users get "out of memory" messages (or "hang") by testing to determine the percentage of free resources a particular inter-application operation needs to complete successfully. Once you've determined this, whenever users execute the command, check in code to see whether the application to be launched is already open. If it's not, make sure that this minimum is available.

When using OLE automation, you can trap the error generated by the GetObject function when the object (such as the Microsoft Excel application) isn't open. (See the section titled The CreatePIVOTTable Function presented later for an example.) If it isn't open, use the CheckResources function (shown below) in Access Basic, VBA for Microsoft Excel, or Visual Basic to verify whether there are sufficient free resources to open the product or a new instance of the product and continue processing (Chapter 6 of Developing Applications with Microsoft Office provides information on using Windows API functions such as GetFreeSystemResources. Remember that you may need to alias API functions).

Declare Function GetFreeSystemResources Lib "User" (ByVal     fuSysResource As Integer) As Integer

Function CheckResources ()
    Dim intFree As Integer
'Specify the go-ahead threshold
    Const THRESHOLD = 65

'The argument 0 specifies system resources; 1 specifies
'graphics resources (GDI); and 2 specifies User resources.
    intFree = GetFreeSystemResources(0)
    If intFree > THRESHOLD Then
        MsgBox "It's a GO!", 64, "Check Resources"
    Else
        MsgBox "NO GO. Resources only " & intFree & "% free.", 16, "Check Resources"
    End If

End Function

Although Word isn't an OLE automation client, you can control other applications through WordBasic and DDE. Use the FindWindow Windows API function to determine whether an Office product is open. Use following WordBasic code to check free system resources and then to launch the product or to warn users depending on the resources available.

Sub MAIN
'The argument 25 specifies the system ersources.
    FreeResources$ = GetSystemInfo$(25)
    MsgBox "Available disk space: " + FreeResources$ + " %."
End Sub