Visual Basic Expert Solutions

book coverQUE

Chapter 13

OLE Container Classes

By Edward B. Toupin


In the previous two chapters, we discussed the basics of OLE, OLE servers, and the implementing of object applications in Visual Basic. Throughout those chapters, references were made to client and controlling applications to provide you with an understanding of why certain operations are performed in a server. In this chapter, we will take your knowledge of servers and your exposure to clients and delve into the development of Visual Basic client container and controlling applications.

To demonstrate the development of Visual Basic client applications, we will develop several types of clients using different methods of integration between OLE servers and OLE clients. The following is a list of concepts that you will learn as you read through this chapter:

What is an OLE Client?

OLE clients are applications that query and use the services of OLE servers. Clients can use these services by incorporating objects from, or the functionality of, server applications. For linking and embedding operations, those clients that incorporate objects are known as containers; for automation, those clients that incorporate the functionality are known as controlling applications.

In many circumstances, OLE clients, containers, and controlling applications provide a means of integrating multiple applications to supply one single-user interface for the integrated applications. For example, as shown in figure 13.1, you can incorporate the functionality of a Word for Windows document, an Excel worksheet, and an Excel chart. Instead of separately accessing each object in its respective application, you can have access to the functionality of all objects in one single application.

Fig. 13.1 OLE clients can be used to integrate OLE server applications to provide one consolidated application.

OLE container applications that support this single-user interface concept can support object embedding, object linking, or both. To assist in developing OLE container applications, Visual Basic provides you with the OLE control that gives you a simple means of inserting and manipulating embedded or linked objects. For embedded objects, if the embedded object's server supports in-place activation, visual editing can be performed on objects within the OLE control.

Controlling applications provide a means of interfacing with the objects of object applications developed in Visual Basic as well as any other Windows applications that expose their objects. You can perform OLE automation using Object variables programatically as well as through the OLE control. Object variables provide you with a means of storing the object of a created automation class with CreateObject() and GetObject(). You can then manipulate the object referenced by the Object variable you created.

The OLE control previously mentioned also provides you with a means of manipulating objects that are either embedded or linked into the control itself. In a manner similar to that of any Object variable you may create, you can access the methods and properties of the object within the control.

From your knowledge of server application development you will notice that client application development is much easier to accomplish. Since server applications maintain much of the code used by client applications, all you have to do is access a class, create an object, and access the methods and properties of the server.

The OLE Control and the Client

Container applications and some controlling applications that support linking, embedding, and OLE automation incorporate objects from server applications using the Visual Basic OLE control. The OLE control is a Visual Basic control that provides a programmable interface to embedded and linked objects and their servers. Primarily, this control provides you with a means of embedding and linking objects. Secondly, you can perform automation operations on the object in an OLE control through methods and properties of the control.

To start the development of an OLE container or controlling application, you must load Visual Basic and create a new form by choosing File, New. Once selected, Visual Basic will clear its workspace and create a new, empty form. You then incorporate the OLE control to manage embedded objects in a Visual Basic application. This control is located in the Toolbox, as shown in figure 13.2.

Fig. 13.2 Once an OLE control is placed into a form, the Insert Object dialog box allows you to select an object to embed into the control.

To place the control, follow these steps:

1. Select it from the Toolbox with your mouse.

2. Move your mouse over to the form and click and hold the left mouse button.

3. Move the mouse until the control is sized appropriately in the form.

4. Once you release the mouse button, the Insert Object dialog box appears, allowing you to select a class of object to embed or link into your OLE control.

You have access to the verbs of the OLE control by clicking the right mouse button on the control. With these verbs you can embed or link objects to the OLE control even after the control is positioned on the form. As you can see in figure 13.3, a menu appears that allows you to select a class to link or embed.

Fig. 13.3 The OLE control allows you access to the verbs of the embedded or linked object.

You can also perform linking and embedding programatically using the methods and properties of the OLE control. The following sections outline some of the methods, properties, and events for the OLE control and provide some brief explanations for each.

Methods

The methods of the OLE control are functions that can be called by your application to perform actions on the control and the object within the control. The list below is a partial list of methods available for the OLE control.

Note: Verbs are the set of programmatically invoked operations supported by a particular object. The set of object-specific verbs can differ for each object type because different verbs make sense for different objects.

Drag begins, ends, or cancels a drag operation of any object.

FetchVerbs retrieves a list of verbs, or operations, that can be performed on an object within the control.

Properties

Properties of the OLE control allow you to set the characteristics as well as determine the operational state of the control programatically. The following list describes the available properties for the OLE control:

Note: The Action and Verb properties are only available for compatibility with earlier versions. It is suggested that appropriate methods of the OLE control be used over the assignment of verbs to the Action and Verb properties.

Events

The following list outlines some of the events that are executed for the OLE control:

Embedding

Visual Basic provides you with two different methods for embedding objects in an OLE control. The first method is through the Insert Object dialog box, as you have seen, and the second is programatically through controls and code. The code for managing embedded objects within the OLE control allows you to pass object names to functions to incorporate these objects into your application. The two methods available with the OLE control for managing embedded objects are InsertObjDlg() and CreateEmbed().

The InsertObjDlg() method of the OLE control programatically accesses the Insert Object dialog box. In this application, the Name property of the OLE control is OLEControl. The statement OLEControl.InsertObjDlg() uses the method of OLEControl to display the dialog box shown in figure 13.3 so that you can select a class to use for an object in the OLE control.

Private Sub EmbedDialog_Click()
'Set the allowable OLE type (Embed)
OLEControl.OLETypeAllowed = 1

Note: The dialog box that appears with InsertDlg() is the same dialog box that appears when you first place the OLE control on the form or access the menu with the right mouse button.

The OLETypeAllowed property allows you to specify the type of object that can be created for the OLE control. This application specifies, with a value of 1, that the OLE control will allow embedded objects only. You can also specify 0 for linked or 2 to allow either type of object for the OLE control.

'Specify how the object is to be activated (Double Click)
OLEControl.AutoActivate = 2
'Access the insert dialog box to create an embedded object
OLEControl.InsertObjDlg
End Sub

The AutoActivate property specifies how the object is to be activated by the user. This application specifies, with a value of 2, that the server application for the object be activated by double-clicking on the object in the OLE control (the default). If you specify 0, the server application for the object can be activated manually using the DoVerb(-1) method, or Verb = -1 property, of the OLE control. A value of 1 specifies that the server application for the object be activated when the object gains focus. Finally, a value of 3 specifies that the server application for the object be activated based on the object's normal method of activation whether the control receives the focus or the user double-clicks the control.

Fig. 13.4 The Insert Object dialog box can be used to select an object for embedding.

The CreateEmbed() method allows you to create an embedded object from a file. The first parameter of CreateEmbed() is the name of the disk-based document that you wish to embed into the OLE control. The optional second parameter is a class that allows you to specify a class to which you wish the object to be assigned. The class can be, for example, Excel.Sheet or Word.Document.

Private Sub EmbedMethod_Click()
'Set the allowable OLE type (Embed)
OLEControl.OLETypeAllowed = 1
'Create an embedded object using the file
' specified.
OLEControl.CreateEmbed "C:\TEMP\TESTSHT.XLS"
'Display the class of the object in the
' OLEControl
MsgBox OLEControl.Class
End Sub

The MsgBox statement uses the Class property of the OLE control (OLEControl) to display the class of the object embedded in the control. In this application, the MsgBox statement will display the class name of the sheet (Excel.Sheet), as listed in the system registry.

Fig. 13.5 Using the system registry for an Excel worksheet, you can embed a Sheet object into the Visual Basic OLE control.

Note: Since you can only perform visual editing on embedded objects, in-place activation, visual editing, and embedded objects go hand-in-hand. Performing in-place activation on embedded objects is actually a function of the server application and can be used by the OLE control.

Using the Clipboard

You can also perform embedding operations using the Clipboard with the Paste method of the OLE control. The paste operation is similar to the Edit, Paste operation of many Windows-based applications. Any object that resides in the system Clipboard with a format acceptable by the OLE control can be pasted into the control.

Note: The formats used in the system Clipboard specify the type of information located in the Clipboard. For example, you can have text, bitmaps, metafiles, and DDE link formats available for use by container applications.

To perform the paste, we must first establish that we want the control to accept the paste from the Clipboard as an embedded object. This is done by setting the OLETypeAllowed property to 1. As we did in the previous section of this chapter, we set the AutoActivate property to 2 to activate the object's server application when you double-click on the object in the OLE control.

Private Sub EmbedPaste_Click()
'Set the allowable OLE type (Embed)
OLEControl.OLETypeAllowed = 1
'Specify how the object is to be activated (Double Click)
OLEControl.AutoActivate = 2

Once the parameters for the paste operation are established, we have to check to see if the OLE control will accept the type, or format, of object currently in the Clipboard. By checking to see if the PasteOK property of the OLE control is True, we are letting Visual Basic check to see if it can handle the system Clipboard's data for a paste. Primarily, the OLE control can handle text, bitmaps, metafiles, and standard OLE types.

'If the control allows pasting
If OLEControl.PasteOK = True Then
'Paste from the clipboard
OLEControl.Paste
End If

If the OLE control is able to embed the object in the Clipboard into the OLE control, you can issue a call to the Paste method of the OLE control with OLEControl.Paste. This method will retrieve the data from the Clipboard and embed it into the OLE control (see fig. 13.6).

'If an error occurs issue a msg to the user
If OLEControl.OLEType = 3 Then
MsgBox "Unable to Paste"
End If
End Sub

Once the paste operation is complete, we check to see if it was successful by checking the OLEType property of the control. If OLEType equals the type that you specified in OLETypeAllowed, which is a value of 1 in this application, then the operation was successful. If the value of OLEType is 3, the paste was unsuccessful and the control deletes any object that may have previously existed in the control.

Note: You can also use the Copy method of the OLE control to copy the contents of the control to the system Clipboard for use by other applications.

Fig. 13.6 The OLE control allows you to paste objects that had been copied to the Clipboard by other applications.

Linking

Container applications that support linking provide a means of incorporating files, saved by server applications, into the container application. Developing an application to support linking is similar to that of embedding. The primary difference in the coding has to do with the methods used and the values of the properties.

As with embedding, the Insert Object dialog box can be accessed and used to select a file to link. To specify that linking is to be allowed by the OLE control, the OLETypeAllowed property is set to a value of 0. We also want the server application for the object to be activated when you double-click the object, so we set the AutoActivate property to 2.

Private Sub LinkDialog_Click()
'Set the allowable OLE type (Link)
OLEControl.OLETypeAllowed = 0
'Specify how the object is to be activated (Double Click)
OLEControl.AutoActivate = 2

The UpdateOptions property of the control allows you to specify how the link is to be updated when the linked object's data is changed by the object's server. 0 specifies that the link is to be updated automatically any time the data changes. 1 specifies that the link is updated only when the file is saved by the server application for the object. 2 specifies a manual update that only occurs when you use the Update() method of the control.

'Automatic updating of link
OLEControl.UpdateOptions = 0
'Access the insert dialog box to create a linked object
OLEControl.InsertObjDlg
End Sub

Fig. 13.7 The Insert Object dialog box can be accessed programatically and used to link objects to the OLE control.

Notice the difference between the dialog box in figure 13.7 and the dialog box for embedding in figure 13.4. The dialog box in figure 13.7 appears because you set the OLETypeAllowed to allow only linked objects. Since this parameter is set, you can only perform linking to objects of files.

As with embedding and the CreateEmbed() method, you can programatically link to objects using a method called CreateLink(). CreateLink() creates a link to a file specified as a parameter to the method. As with CreateEmbed(), you specify the source document and, optionally, the class of the object to which you wish to link.

Private Sub LinkMethod_Click()
'Set the allowable OLE type (Link)
OLEControl.OLETypeAllowed = 0
'Create a linked object using the file specified.
OLEControl.CreateLink "c:\temp\TESTSHT.XLS"
End Sub

Using the Clipboard

Linking data from the Clipboard is more a function of DDE than it is of OLE, but it is a feature made part of the Visual Basic OLE control. Container applications that support linking with system Clipboard information allows the user to provide a link to a running server application.

Note: When you perform a link with an object in the system Clipboard, you are actually establishing a link to the object's server. In this situation, the object's server should be running and the object in the Clipboard would have been placed there using the Edit, Copy operation of the server application.

As we discussed with embedding, you should set the OLETypeAllowed property for the control to accept linked objects. We also set the AutoActivate property to activate the object's server on a double-click and the UpdateOptions property to update automatically whenever the object's data changes.

Private Sub LinkPaste_Click()
'Set the allowable OLE type (Link)
OLEControl.OLETypeAllowed = 0
'Specify how the object is to be activated
' (Double Click)
OLEControl.AutoActivate = 2
'Automatic updating of link
OLEControl.UpdateOptions = 0

The Paste operation for linking is identical to the Paste operation for embedding. Using the PasteOK property, we check to see if the control can handle the type of object in the Clipboard as well as determine if the object's server is running. If the format is correct and the server is running, we Paste a link to the object into the OLE control.

'If the control allows pasting
If OLEControl.PasteOK = True Then
'Paste from the clipboard
OLEControl.Paste
End If
'If an error occurs issue a msg to the user
If OLEControl.OLEType = 3 Then
MsgBox "Unable to Paste"
End If
End Sub

Fig. 13.8 The OLE object allows you to programatically paste links to objects that have been copied to the Clipboard from other applications.

Pasting Special Objects Using the Clipboard

An alternative to using the Paste method of the OLE control is using the PasteSpecialDlg method. This method allows you to specify—using the Paste Special dialog box—the version or format of an object in the Clipboard you want to paste into the OLE control.

In the following code, we are performing the Paste operation on data from a dialog box from which we can select the type of data. By setting the OLETypeAllowed to 2 in this application, we are allowing either linked or embedded data to be placed into the control.

Private Sub PasteSpecial_Click()
'Set the allowable OLE type
OLEControl.OLETypeAllowed = 2
'Specify how the object is to be activated
' (Double Click)
OLEControl.AutoActivate = 2
'Automatic updating of link
OLEControl.UpdateOptions = 0

The primary difference between the code used for pasting in the application and in the embedding or linking application is in the call to PasteSpecialDlg. This method loads a dialog box (see fig. 13.9) from which you can select the format of the data you wish to paste into the control.

'If the control allows pasting
If OLEControl.PasteOK = True Then
'Paste from the clipboard
OLEControl.PasteSpecialDlg
End If
'If an error occurs issue a msg to the user
If OLEControl.OLEType = 3 Then
MsgBox "Unable to Paste"
End If
End Sub

Fig. 13.9 The Paste Special dialog box allows you to select the format of the data that you wish to paste into your OLE control.

As you can see in figure 13.9, you can either embed or link the object by selecting either the Paste or Paste Link radio button. To paste the object into the OLE control, simply double-click the desired object or select the object and click OK. If you select Display As Icon, an icon will appear (see fig. 13.10) in the OLE control representing the server that will load the object (file) named below the icon.

Fig. 13.10 An icon can be placed into the OLE control to represent the server that will open when you wish to load and edit the linked object.

Verbs

Verbs are the set of programmatically invoked operations supported by a particular object. The set of object-specific verbs can differ for each object type because different verbs make sense for different objects. For example, a video object's verbs might include play and edit, while a text object might only support edit. So that other Windows applications will know what types of verbs are supported, an object application registers object-specific verbs (see fig. 13.11) in the registration database for each type of object supported.

Fig. 13.11 Servers register their verbs in the system registry so that container applications can have access to the available actions of the server.

As you can see in figure 13.12, by clicking the right mouse button over Microsoft Video for Windows in the OLE control, a small menu appears that contains the verbs specific to the object. Referring back to figure 13.11, you can see that the two verbs are registered as 0 for Play and 1 for Edit. Selecting Play in figure 13.12 plays the video object in the OLE control while selecting Edit allows you to edit the object in-place.

Fig. 13.12 The Microsoft Video for Windows server allows containers to play and edit video objects.

If you prefer, you can programatically manipulate the object using the method called DoVerb() for OLE control. As you saw in the list of methods at the beginning of this chapter, DoVerb() allows you to select a verb to be executed based on the verb's position and value in the system registry.

OLEControl.DoVerb (0)

We know, from the system registry, that Play is assigned a value of 0 at the 0 offset of the verb list. To execute the specific verb, you merely use the OLE control (named OLEControl in this example) and call the DoVerb() method with the value of the Play verb.

Note: You can execute verbs with the DoVerb() method or the Verb() property procedure. The syntax for the members are DoVerb(verb_val) and Verb = verb_val. The parameter verb_val is the value of the verb as registered in the system registry.

Controlling Applications

As you saw in the previous chapter, "OLE Servers," object applications allow you to provide a service through exposed objects for use by controlling applications. In this chapter, we will create a controlling application that will access our object application from the previous chapter as well as other applications that expose their objects for use by controlling applications. There are two methods for using OLE automation on object applications—automation using objects and automation using the OLE control.

Automation Using Objects

In the previous chapters, we briefly touched on the use of objects to develop controlling applications to perform OLE automation in Visual Basic. Recall that object applications are registered in the system registry. Through the registry and the library for the object application you can see what classes, methods, and properties are available with the Object Browser (which is accessible from the View menu in Visual Basic).

In this section, we will develop a controlling application that uses the AutoSrvr object application we developed in the previous chapter. To find out what object, methods, and properties are available to us, we will first use the Object Browser to look at the AutoSrvr object application.

Fig. 13.13 AutoSrvr.Calc's methods and properties, as listed in the Object Browser, allow you to perform automation operations on the object.

As you can see in figure 13.13 and figure 13.14, and as you already know from the previous chapter, the AutoSrvr object application provides us with two classes: Calc and Picture. The Calc class contains three members: Mult(), ThisText, and Value. Selecting the Picture class with your mouse reveals the member DispPicture(). Now that we know what is provided to us by AutoSrvr, we can use these classes in controlling applications.

In the following event procedure, we first declare a variable of type Object that is used throughout the procedure to manage a class instance of Calc. We then create a class instance with the CreateObject() function for the class Calc of the object application AutoSrvr.

Private Sub CalcClass_Click()
'Dim an object variable
Dim AutoSrvr As Object
'Create an object of the Calc class
Set AutoSrvr = CreateObject("AutoSrvr.Calc")

To utilize the methods and properties of the object, we use the Mult() method to multiply two times three. The result is converted to a string and printed in a dialog box with the MsgBox statement.

'Display the result of 2 * 3
MsgBox Str(AutoSrvr.Mult(2, 3))

Recall the property procedures Get Value and Let Value from the AutoSrvr object application. Here we use the Let Value procedure to assign the value 10 to a private property. In the Object Browser from figure 13.13, the private properties were not listed; however, the property procedure Value was listed. Recall that we declared ThisNum as a private property when we developed the object application. The Get Value procedure is used with the Str() function to print the result of the property procedure in a dialog box with the MsgBox statement.

'Set the Value to 10 then display
AutoSrvr.Value = 10
MsgBox Str(AutoSrvr.Value)
'Set ThisText the display
AutoSrvr.ThisText = "This is a test"
MsgBox AutoSrvr.ThisText
End Sub

To end the procedure, we access the ThisText public property directly and assign a string to the variable. We then print the value stored in the public variable of Calc in a dialog box using the MsgBox statement.

The Picture class is treated in the same manner as the Calc class. As you can see in the following code, we first declare an Object variable in which we will store an instance of the class Picture. Using the CreateObject() function, we create an object so that we can access the methods and properties of the class.

Private Sub PictureClass_Click()
'Dim an object variable
Dim AutoSrvr As Object
'Create an object of the Picture class
Set AutoSrvr = CreateObject("AutoSrvr.Picture")
'Call the method to display a picture
AutoSrvr.DispPicture ("C:\WINDOWS\ARCHES.BMP")
End Sub

From the Object Browser, we see in figure 13.14 that the Picture class maintains only one method: DispPicture(). To use this method, we pass the name of the picture we want to display in the parameter line of the method.

Fig. 13.14 AutoSrvr.Picture's methods and properties, as listed in the Object Browser.

Now let's apply what we accomplished for AutoSrvr to Microsoft Excel and take OLE automation with Visual Basic a few steps further. The first thing that we have to do is look in the Object Browser to find the objects, methods, and properties that are exposed for our use. Since we are going to create a worksheet object, we should look at the exposed methods and properties of the Worksheet class (see fig. 13.15).

Fig. 13.15 Excel.Worksheet's methods and properties, as listed in Object Browser.

Note: There are two methods available to locate the classes for a given application. The first method is by looking in the system registry using the REGEDIT application. The second method is to use the Object Browser to browse the object library for the application. REGEDIT contains the visible classes that are accessible from controlling applications; the Object Browser shows the public classes available for your access.

Our application (see fig. 13.16) will create a worksheet, set the values of several cells, then calculate the sum of the values and place that sum in a cell.

Fig. 13.16 OLE automation can be performed on Excel from Visual Basic.

In Listing 13.1, we first dimension an object variable globally that will be used to access our worksheet object. When you click the Create Worksheet button control, the event procedure CreateSheet_Click() is executed. This event procedure creates an instance of an Excel worksheet and assigns the instance of the worksheet to the Sheet object variable.

Listing 13.1 OLECLNT3 Creating an Instance of an Excel Worksheet and Assigning it to the Sheet Object Variable

‘OLECLNT3
'Dim an object variable
Dim Sheet As Object
'Create a new worksheet object
Private Sub CreateSheet_Click()
Set Sheet = CreateObject("Excel.Sheet")
'Make the sheet and the application visible
Sheet.Visible = True
Sheet.Application.Visible = True
End Sub

Once the worksheet object is created, we want to make the Excel application and the instance of the sheet within Excel visible so that we can watch the results of the remainder of the procedures in our application. To make the object visible, we set the Visible property of the application and the sheet to True to make the Excel application and the new sheet visible. If you want to hide the application, set the Application.Visible property to False. To hide the worksheet, set the Sheet.Visible to False.

When the SetValues_Click() event procedure is executed, as a result of clicking the Set Values button control, we use the Sheet object and assign values to the cells of the active worksheet of the application. The Cells() method allows you to access the cell collection of the worksheet using two parameters: the row and the column—Cells(row,col). This method applies to the worksheet that we created with the CreateObject() function. Value is a variant property procedure that allows us to set the values of the cells as well as retrieve the values of cells for use in the application (see Listing 13.2).

Listing 13.2 OLECLNT4 Using the Value Variant Property Procedure

‘OLECLNT4
'Set the values of the cells. Set each value
' in a column (A1,A2,A3,A4,A5)
Private Sub SetValues_Click()
'Row 1, Col 1
Sheet.Cells(1, 1).Value = 1
'Row 2, Col 1
Sheet.Cells(2, 1).Value = 2
'Row 3, Col 1
Sheet.Cells(3, 1).Value = 3
'Row 4, Col 1
Sheet.Cells(4, 1).Value = 4
'Row 5, Col 1
Sheet.Cells(5, 1).Value = 5
End Sub

In the Listing 13.3, we add something a little different to demonstrate how to use a different OLE automation function—GetObject(). In this event procedure, executed when you click the Calc Sum button control, we first dimension an Object variable for use locally within the procedure. This variable is used with the GetObject() function to obtain a reference to the active sheet we created with the CreateObject() function.

GetObject() takes two parameters: a path and a class—GetObject(path,class). If path is a zero-length string (""), GetObject() returns a new object instance of the specified type in a manner similar to that of CreateObject(). If the pathname argument is omitted entirely, as in this application, GetObject() returns the currently active object of the specified type.

Note: Use the GetObject() function when there is a current instance of the object or if you want to create the object with a file already loaded. If there is no current instance, and you don't want the object started with a file loaded, use the CreateObject() function.

Caution: If a class has been registered as a single-instance object (for example, the Word.Basic class for Microsoft Word for Windows 6.0), only one instance of the object is created regardless of the number of times CreateObject() is executed. Note, however, that with a single-instance object, GetObject() always returns the same instance when called with the zero-length string syntax ("") and it causes an error if the path argument is omitted.

Listing 13.3 OLECLNT4 Using the GetObject() OLE Automation Function

‘OLECLNT4

'Calculate the sum of the values

Private Sub CalcSum_Click()

Dim XLApp As Object

Set XLApp = GetObject(, "Excel.Application")

XLApp.Cells(7, 1).Value = "=SUM(A1:A5)"

XLApp.Cells(7, 1).Font.Bold = True

End Sub

Using the worksheet reference we obtained in the XLApp object variable, we assign an equation to the cell at row 7, column 1. Since Value is a variant variable, we can assign equations as strings as simple as we can assign integers or floating-point decimals. The string equation "=SUM(A1:A5)" calculates the sum of the five values that we placed into the worksheet in the previous event procedure.

To emphasize the total of the values, we bold the value of the sum using the Font.Bold property of the cell. You can also change fonts, change styles, and change the point of fonts as required by accessing the methods and properties of the exposed objects.

Closing the application is just as simple as starting it and creating an instance of one of its objects. You can access the Quit method of the Application object for our application, which will quit the Excel application. If unsaved workbooks are open, Excel displays a dialog box asking if you want to save any changes. You can prevent this by saving all workbooks before using the Quit method or by setting the DisplayAlerts property to False. When this property is False, Excel does not display the dialog box and it quits without saving them. If you set the Saved property to True without saving it to the disk, Excel will quit without asking you to save the workbook.

'Quit Excel
Private Sub CloseSheet_Click()
Sheet.Application.Quit
End Sub

Automation Using the OLE Control

Now let's use the OLE control to implement a controlling application for Excel in the same manner we did when using objects. The one thing that is different is the way you access exposed objects with the OLE control. With the OLE control, you do not have to programatically create an object with CreateObject(); you instead use the OLE control as your object interface.

As with objects, you should refer to the Object Browser to find the available methods and procedures that you can access. However, you do not have to consult the system registry to find the class names of the object application since the OLE control will handle all class references. Recall from our discussions of linking and embedding that you can insert objects into the OLE control using the Insert Object dialog box or programatically. If these objects located in the OLE control support OLE automation, you can manipulate the classes of the object applications programatically.

The form for the application (see fig. 13.17) contains an OLE control called OLEControl. This control contains an embedded Excel worksheet that was embedded using the Insert Object dialog box when the OLE control was placed on the form.

Fig. 13.17 OLE automation can be performed on an embedded object in the same manner as that of a created object.

This control provides us with a method of accessing the functionality of Excel through the Object property of the control. The Object property points to the object in the OLE control and provides us with a reference to the object and its methods and properties. This reference is similar to the reference of a dimensioned object provided after a call to CreateObject("Excel.Sheet") (see Listing 13.4).

Listing 13.4 OLECLNT4 Referencing the Object and its Methods and Properties

'Set the values of the cells. Set each value in a column
‘(A1,A2,A3,A4,A5)
Private Sub SetValues_Click()
'Row 1, Col 1
OLEControl.Object.Cells(1, 1).Value = 1
'Row 2, Col 1
OLEControl.Object.Cells(2, 1).Value = 2
'Row 3, Col 1
OLEControl.Object.Cells(3, 1).Value = 3
'Row 4, Col 1
OLEControl.Object.Cells(4, 1).Value = 4
'Row 5, Col 1
OLEControl.Object.Cells(5, 1).Value = 5
End Sub

As when we used object variables, we reference the Cells method of the Object from the OLE control and set the value for the cells with the Value property.

To calculate the sum of the values that we just placed into the cells, we place the equation using the OLEControl.Object reference. As before, the cell in row 7, column 1 is updated with the equation "=SUM(A1:A5)" and the font is bolded to emphasize the result.

'Calculate the sum of the values
Private Sub CalcSum_Click()
OLEControl.Object.Cells(7, 1).Value = "=SUM(A1:A5)"
OLEControl.Object.Cells(7, 1).Font.Bold = True
End Sub

One noticeable difference between the use of the OLE control and the use of object variables is that the object in the OLEControl is updated to show you the results of the operations. With the object variable, we had to make the application and the sheet visible to view the operations of the code where the OLEControl of this application contains a visible embedded object.

From Here...

In this chapter, we used your knowledge of OLE servers and provided insight into the use of OLE clients. By delving into the development of Visual Basic client container and controlling applications, we discussed several different methods for OLE interaction using Visual Basic. The methods of OLE interaction discussed in this chapter included object embedding, object linking, and OLE automation.

• For more information on the inclusion of controls into you application, see Chapter 20, "Third-Party Controls."

• To investigate the features and functionality of Windows 95 custom controls, see Chapter 20, "Third-Party Controls."

• For information covering the inclusion of database management in your application, see Chapter 7, "The Jet Engine and ODBC."


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.