OLE Automation goes one step beyond linking. When you link, you borrow the functionality of another application. When you use OLE Automation, you control the source document from within your application. Applications that support OLE Automation expose their objects to other applications. For example, an object that Word exposes could be a sentence, a paragraph, or an entire document. An object that Excel exposes could be a macro, a range of cells, or an entire worksheet.
You can control exposed objects from your Visual Basic application via the properties and methods exposed by the source application, An important feature of Visual Basic is that you can both access the objects of an OLE server application and use the functionality of the server application. The benefit of OLE Automation is that you can work in a single environment and use any OLE tools that are available from other applications.
When Microsoft developed OLE Automation, their basic idea was really simple: create a common language and programming environment for a number of applications so that people could customize applications and add capabilities to suit their own environments. The result was the language Visual Basic for Applications.
You can’t use VBA to develop any type of application you may need or think of. VBA provides only basic control structures, math and string functions, and variable manipulation capabilities. The real power of VBA comes from the objects of the applications that support it.
With the introduction of VBA5, Microsoft started licensing the language to manufacturers who wanted to add programmable. features to their products, for example, Autodesk’s AutoCAD. AutoCAD had been a programmable environment for many years, but its programming language was unique to AutoCAD and couldn’t be shared with other applications. Many other manufacturers included scripting languages or other means of automating their software, but the need for a global language that could act as the glue in putting together pieces of many applications was dear. Finally, Microsoft came up with a version of VBA (version 5) that met the needs of other manufacturers. VBA is now on its way to becoming a universal language for automating applications under Windows.
Most businesses today buy off-the-shelf software and need to customize it. More than half the corporations in the United States use Microsoft Office products. Many . of them use VBA to customize these applications to suit their specific business needs. This trend will continue and become stronger in the future. There is already a need not only to customize applications, but to tie them together so that they can communicate. VBA serves both functions, and as a result, the need for VBA programmers will increase in the next few years.
Today’s applications are so powerful and feature-rich that it no longer makes sense to develop custom applications. Even the Office 97 applications are adequate for addressing most of the day-to-day computer operations of a typical corporation. With a host of third-party applications supporting VBA, you can easily guess its importance in corporate environments. Let’s start our exploration of OLE Automation by looking at a few examples.
Contacting an OLE Server
To access the services of an OLE server application, you must first create a variable that references Excel. This variable is called an object variable, because it represents an object rather than an integer or other simple data types. The characteristic of object variables is that they expose the members of the application or Class that they represent. Excel, for instance, exposes the Evaluate method, which lets you evaluate any math expression. This method can’t be accessed directly; it must be accessed through an object variable that represents the Excel application. In other words, you can’t use a statement like:
You must first create an object variable, for instance, ExcelObj, and then call the ExcelObj variable’s Evaluate method:
There are two functions that create object variables. The CreateObject() function, which creates a new instance of the application you want to reference, and the GetObject() function, which contacts an instance of the application that’s already running on the computer. These functions are explained next.
Creating a New Instance
The first method of contacting an OLE server application is to start a new instance of the application with. the CreateObject() function, whose syntax is:
Class is the server application’s Class name as it’s registered in the Registry. The Class name of Word is the string “Word.Application” and the Class name of Excel is “Excel.Application”. This is the Class argument of the CreateObject() function.
The second argument is optional and it’s the name of the network server on which the object will be created. If the server application resides in another machine; you must also specify the name of the machine on which the Class will be created. If the server application has been installed on the machine “Toolkit,” then use the following statement to create a new instance of Excel:
Contacting an Existing Instance
If the application you want to contact is running already, there’s usually n? reason to start a new instance. You can contact the running instance of the application and open a new document. To contact a running instance of a server application use the GetObject() function, whose syntax is:
Set AppObject = GetObject(pathname, class)
Both arguments of the GetObject() are optional, but one of them must be specified. The full path and file name of the document to be opened with the server application is pathname. The second argument, class, is the name of the application’s Class as it’s registered with the system Registry.
If you specify a document to be opened by the application, you don’t have to supply the application’s Class name. For example, you don’t have to specify that a worksheet be opened with Excel. The system knows that files with extension XLS are handled by Excel, for example:
The previous statement will also start Excel and load the specified file if no instance of Excel is running at the time. ‘Finally, to contact the running instance of Excel use the statement: .
In many situations, it doesn’t make any difference whether you start a new instance of the server application or contact an existing one. To call the Evaluate method of Excel, for instance, you don’t need a new instance of Excel. If one is running already, you can contact it to evaluate the math expression. After this action completes, the running instance of Excel is in the exact same state as before. Therefore, it’s more efficient to attempt to contact an existing instance of the server application. Only if there are no currently active instances should you start a new instance. The following code segment does exactly this. It attempts to contact a running instance of Word. If Word isn’t running at the time, a runtime error is generated. The On Error Resume Next statement . causes Visual Basic to suppress the error message and continue with the statement that follows, which examines the Err object. If an error occurs, then it knows that the GetObject() function failed-and it uses the CreateObject() function to start a new instance of Word.
Declaring Object Variables
The object variable that represents an OLE server application can be declared either as Object or as a specific type (like Excel.Application or Word.Application). If you declare the EXL App variable as Object, then every time you call its properties or methods, Visual Basic must first make sure these members exist before contacting them. Because Object is a generic variable type that can accommodate all types of objects, Visual Basic doesn’t know which object an object variable represents at design time and it can’t detect syntax errors when you enter the code. For example, if you mistype a member name, Visual Basic won’t catch it at design time. Excel, for example, supports the Evaluate method, which evaluates math expressions. If you declare EXL App as Object and then attempt to access its Calculate method, Visual Basic won’t spot the error .
If you’ve declared the variable EXL App as Excel.Application, then Visual Basic won’t only catch any references to nonexistent members, it will also display the list of members in a list as soon as you type the period following the EXL App variable’s name. In other words, by declaring variables with their proper type, syntax errors are caught as you enter code. The application is not going to crash because you’ve misspelled one of its member’s name. Even if the option Member AutoList is turned off, these errors will be caught during compilation.
Mother far more serious implication of proper object type declaration is performance. When Visual Basic sees an expression like EXL App Property, it must first make sure that the specified property exists, then contact the server application represented by the EXL App object and invoke its Property member. The compiler will produce additional statements that contact the application represented by an object. variable to make sure that it expose!:!the requested member. Even worse, these statements will be executed every time your application requests a member of the EXL App object. To avoid this necessary delay, simply declare the EXL App object variable with its proper type.
Object variables declared with specific types are called early-bound, because Visual Basic can bind them to the objects they represent at design time. Object variables declared generically (as Object) are called late-bound. Visual Bask can’t bind them to specific objects at design time and any errors will surface at runtime.
To take advantage of the early binding of object variables, declare them with the proper types. Late binding of variables is not always a bar practice. Sometimes, we don’t know the type of object we’ll store in a variable and our only option is to declare it as Object. For example, you may have to switch between the Form and Printer objects from within your code. An elegant method of printing on either object IS to declare an OutputObject variable and then set it to the desired device with one of the following two statements:
Set OutputObject = Printer
Set OutputObject = Screen
When you issue the Print method of the OutputObject, it applies to the object that was most recently assigned to the OutputObject object variable. In situations like this one, the OutputObject variable can’t be early-bound.
Accessing Excel through an Object Variable
The EXL Object variable is an object variable that exposes the objects of Excel. To access the current workbook, for instance, use the expression:
EXLObject.ActiveWorkbook
To access the currently active sheet in the active workbook, use the expression:
EXLObject.ActiveWorkbook.ActiveSheet
The objects we usually want to access on a worksheet are the cells, which are represented by the Cells collection. To access the first cell of the second row and assign the value 99 to it, use the following expression:
EXLObject.ActiveWorkbook.ActiveSheet.Cells(2, 1).Value = 99
The properties and methods for accessing Excel that give you access to the cells and allow you to manipulate them are discussed later in the chapter. What you should keep in mind is that they are accessed through the variable returned by the CreateObject() function. The CreateObject() function, as well as the GetObject() function, return a reference to an OLE server. This variable is your gateway to the object model exposed by the server application. The object model is a hierarchy of objects that correspond to the objects of the server application (e.g., Excel’s cells . and macros, Word’s sentences and dictionaries).
In the remaining sections of this chapter, I will describe the most basic objects of the Word 97, Excel 97, and Outlook 98 (the beta version that was made available before the release of Visual Basic 6). By the time you read this book, Windows 98 versions of Word and Excel may be.available. The following examples should work without adjustments. If you experience any problems, you’ll find the newer version at Sybex’s Web site, on the page for this book.
Discussing all the objects exposed by these applications would probably require three different books. I’ve limited the discussion to the basic objects exposed by the applications and provide examples that you can experiment with. During your understanding of the basic objects of these applications and the help of the Object Browser, which lists the objects exposed by each application, you should be able to develop interesting applications that rely on the services of OLE server applications.
