Chapter 13

Controlling OLE Objects

You control OLE objects by using the OLE control's properties and methods. All OLE objects share these properties and methods, whether they were created with the OLE control or with an insertable object. This chapter tells you how to control OLE objects in code.

In this chapter, you learn how to do the following:

• Create linked and embedded objects at run time
• Control the display of OLE objects by resizing, scrolling, and scaling
• Enable the user to move or resize embedded objects in place
• Capture the image of an OLE object and display it in a picture box or image control
• Update linked objects
• Activate and deactivate linked and embedded objects
• Save OLE objects in special files that Visual Basic can read directly
• Get the OLE Automation object from linked or embedded objects

Creating OLE Objects at Run Time

To create OLE objects at run time, you use the OLE control. It provides the methods listed in table 13.1 to create objects.

Table 13.1 OLE Control Methods for Creating Objects

Follow these steps to create an OLE object at run time:

1. Draw an OLE control on a form. Visual Basic displays the standard Insert Object dialog box (fig. 13.1).

Fig. 13.1

To create a linked or embedded object at run time, click the Insert Object dialog box's Cancel button.

2. Click Cancel to close the Insert Object dialog box without selecting an OLE object to create.
3. In an event procedure, use the InsertObjDlg, CreateEmbed, or CreateLink methods to create an object for the control, as in the following example:
   Private Sub OLE1_DblClick()
   OLE1.InsertObjDlg ' Let the user choose an object to create.
   End Sub

   These lines display the Insert Object dialog box (fig. 13.1) when the user double-clicks the OLE object control.

Creating an Embedded Object

To create an embedded object without displaying the Insert Object dialog box, follow the procedure in the preceding section, but substitute this code in step 3:

The preceding lines of code embed a new, empty worksheet in the OLE control OLE1. To base the embedded object on a file, specify the file name in the first argument, as in the following example:

If you specify a file name for CreateEmbed, Visual Basic ignores the second argument. If a file contains more than one type of object, Visual Basic simply uses the first object in the file. You cannot specify an object within a file by using CreateEmbed.

Class Types of Embedded Objects

The CreateEmbed method uses the class type of the OLE object to create new embedded objects (the type argument). Table 13.2 lists the class types for some commonly available OLE objects.

Table 13.2 Class Types for Common OLE Objects

The OLE class type differs from the OLE programmatic ID that CreateObject and GetObject methods use. See Chapter 12, "Creating OLE Containers," for more information on CreateObject and GetObject.

Creating a Linked Object

To create a linked object without displaying the Insert Object dialog box, follow the procedure in the section "Creating OLE Objects at Run Time," but substitute this code in step 3:

Obviously, this procedure causes an error if C:\EXCEL\STOCK.XLS does not exist. To prompt a file name from the user, add a Common Dialog control to the form and use the code in listing 13.1.

Listing 13.1 Getting a File Name to Create a Linked Object

The function FileExists verifies that the user entered a valid file name in the Open File dialog box. Using FileLen and error checking is one of the faster ways of checking for a file, although it certainly is not the only way.

Controlling the Display of OLE Objects

One of biggest problems with creating OLE objects at run time is getting them to display correctly on screen. When you create objects at design time, you draw the object and can adjust its appearance manually. When you create OLE objects at run time, you must write code to handle the object's display. To handle the display of OLE objects at run time, you can use several techniques:

• Resize the control to fit the object
• Scroll the object on the form
• Scale the object to fit in the control
• Scale the object and size the control to create the best fit for both the object and the control

The following sections describe how to use the OLE control's SizeMode property and Resize event to perform these programming tasks.

How the SizeMode Property and Resize Event Interact

The OLE control's SizeMode controls how the OLE control displays an OLE object. Table 13.3 lists the possible settings for the SizeMode property.

Table 13.3 SizeMode Property Settings

If the OLE control does not match the size of the OLE object that it contains, Visual Basic triggers a Resize event for the object. The Resize event has the following form for the OLE control:

The HeightNew and WidthNew arguments indicate a recommended size for the object. This size is that of the OLE object in its original form. You can use these arguments with the SizeMode property to control the scaling of an OLE object. To learn more about this technique, see the section "Scaling the Object to Fit the Control," later in this chapter.

The Resize event occurs when the displayed size of an OLE object changes, as in the situations described in table 13.4.

Table 13.4 Situations That Result in a Resize Event

The Resize event does not occur when SizeMode changes from any setting to vbOLESizeClip or vbOLESizeAutoSize to any setting. In the latter situation, the control size matches the object size, so no visual change occurs either.

Displaying Objects in a Sizable Window (Form)

A simple way to size an OLE object is to display it on a separate form that matches the size of the OLE control, and then enable the user to resize the form. Use the ScaleMode settings vbOLESizeClip or vbOLESizeAutoSize when creating OLE objects to display in a sizable window.

To see how this scheme works, follow these steps:

1. Create a new project.
2. Add an OLE control to Form1 and move the control so that its upper-left corner is in the form's upper-left corner.
3. Add the following lines of code to the Form1_Load event procedure:
   Private Sub Form_Load()
   ' Automatically adjust the control to fit the object.
   OLE1.SizeMode = vbOLESizeAutosize
   ' Display the Insert Object dialog
   ' to get an object to display.
   OLE1.InsertObjDlg
   End Sub

4. Add these lines of code to the Form1_Resize event procedure:
   Private Sub Form_Resize()
   ' Prevent the form from exceeding
   ' the size of the OLE object.
   If OLE1.Width < Form1.Width Then Form1.Width = OLE1.Width
   If OLE1.Height < Form1.Height Then Form1.Height = OLE1.Height
   End Sub

5. Run the project and select an OLE object to display. The object appears on the form as shown in figure 13.2.

Fig. 13.2

When you resize a form, you can see more of the object. This "object in a window" approach is good for multiple-document interface (MDI) applications.

Scrolling the Control for Large Objects

Another way to size objects is to enable the user to scroll large objects up, down, right, or left. To create scrollable OLE objects, you use the ScaleMode setting vbOLESizeAutoSize, as described in these steps:

1. Create a new project.
2. Draw horizontal and vertical scroll bars on the form and an OLE control (see fig. 13.3).

Fig. 13.3

3. Add the following lines of code to the form's Load event procedure:
   Private Sub Form_Load()
   ' Use the default SizeMode, OLE control
   ' automatically resize to match the object's size.
   OLE1.SizeMode = vbOLESizeAutoSize
   ' Display the InsertObject dialog box to
   ' let the user choose anobject to embed or link.
   OLE1.InsertObjDlg
   End Sub

4. Add the following lines of code to the scroll bars' Scroll event procedures. These lines scroll the OLE object on the form when the user moves either scroll bar.
   Private Sub HScroll1_Change()
   OLE1.Left = 0 - HScroll1.Value
   End Sub
   Private Sub VScroll1_Change()
   OLE1.Top = 0 - VScroll1.Value
   End Sub

5. Add the following lines of code to the OLE control's Resize event procedure. These lines control the scroll bars' display and determine the scroll bars' scale and maximium values based on the OLE object's size.
   Private Sub OLE1_Resize(HeightNew As Single,
   WidthNew As Single)
   If HeightNew > Form1.Height Then
   VScroll1.Visible = True
   VScroll1.Max = HeightNew
   VScroll1.LargeChange = _
   HeightNew / (HeightNew / OLE1.Height)
   VScroll1.SmallChange = VScroll1.LargeChange / 10
   Else
   VScroll1.Visible = False
   End If
   If WidthNew > Form1.Width Then
   HScroll1.Visible = True
   HScroll1.Max = WidthNew
   HScroll1.LargeChange = WidthNew / _
   (WidthNew / OLE1.Width)
   HScroll1.SmallChange = HScroll1.LargeChange / 10
   Else
   HScroll1.Visible = False
   End If
   End Sub

6. Add the following lines of code to the form's Resize event procedure. These lines trigger the OLE control's Resize event to elicit the correct behavior when the user resizes the form.
   Private Sub Form_Resize()
   ' Skip first Resize on Load.
   Static bFlag As Boolean
   If bFlag Then
   ' If form resizes, trigger OLE control
   ' resize behavior.
   OLE1_Resize OLE1.Height, OLE1.Width
   Else
   bFlag = True
   End If
   ' Call support procedure to adjust the placement
   ' and size of scroll bars on the form.
   AdjustScrollBars Me
   End Sub

7. Run the project. When displaying on the form, the OLE object appears with scroll bars if it is larger than the current form, as shown in figure 13.4.

Fig. 13.4

After creating the sample in this section, you can resize the form or scroll it to see more of the object.

In step 6, you use the support procedure in listing 13.2 to adjust the scroll bar's positions on the form. This procedure is useful in a variety of contexts, so the companion CD presents it as a separate procedure instead of building it in to the form's Resize event.

Listing 13.2 A Procedure for Adjusting Scroll Bars When a Form Is Resized

Scaling the Object to Fit the Control

OLE objects are often (but not always) much larger than the OLE control drawn on a form. Therefore, scaling the object to fit in the control often results in an object that's hard to read on screen.

This type of display is useful when legibility is not an issue or when you are displaying small objects, such as small graphics. Use the ScaleMode settings vbOLESizeZoom or vbOLESizeStretch when scaling objects to fit the OLE control.

Usually vbOLESizeZoom is more useful than vbOLESizeStretch, because it maintains the object's original proportions.

Zooming an Object for the Best Fit

Scaling enables you to fit more on screen than other display methods. However, to create legible objects, you must combine scaling with sizing the OLE control. To create a best fit, use the SizeMode setting vbOLESizeZoom, the Resize event, and the Height and Width properties together.

To see how to create the best fit for OLE objects inserted at run time, follow these steps:

1. Create a new project.
2. Add an OLE control to the form and move the control so that its upper-left corner is in the form's upper-left corner. Then add to the form a vertical scroll bar to control zooming. Figure 13.5 shows the desired form.

Fig. 13.5

The scroll bar controls the zoom ratio of the OLE control.

3. Add the following lines of code to the form's Load event procedure. When run, these lines display the Insert Object dialog box that enables the user to select an OLE object to insert in the control.
   ' Declare module-level variables used.
   Dim msHeightRatio As Single, msWidthRatio As Single
   Private Sub Form_Load()
   ' Scale the object to fit the control.
   OLE1.SizeMode = vbOLESizeZoom
   ' Display the Insert Object dialog to get
   ' an object to display.
   OLE1.InsertObjDlg
   End Sub

4. Add the following lines of code to the OLE object's Resize event procedure. This event procedure calculates the ratio of the object's actual size (HeightNew and WidthNew) to the size of the control. It also determines the display and Max value of the scroll bar used to control zooming.
   Private Sub OLE1_Resize(HeightNew As Single, _
   WidthNew As Single)
   ' Choose which ratio is greater.
   msHeightRatio = HeightNew / OLE1.Height
   msWidthRatio = WidthNew / OLE1.Width
   ' If control is big enough to contain object, don't
   ' display scroll bar.
   If msWidthRatio <= 1 And msHeightRatio <= 1 Then
   VScroll1.Visible = False
   ' Exit now.
   Exit Sub
   End If
   ' Use the greater ratio for the scroll bar zoom.
   If msHeightRatio >= msWidthRatio Then
   ' Set the maximum value (100%)
   VScroll1.Max = HeightNew
   ' Set the initial scroll bar position.
   VScroll1.Value = OLE1.Height
   Else
   ' Set the maximum value (100%)
   VScroll1.Max = WidthNew
   ' Set the initial scroll bar position.
   VScroll1.Value = OLE1.Width
   End If
   End Sub

5. Add the following lines of code to the scroll bar's Change event procedure. This code scales the size of the OLE control up or down, and also automatically scales the object displayed in the control to match the control's size.
   ' Zoom OLE control.
   Private Sub VScroll1_Change()
   ' Increase Height or Width, depending on which
   ' ratio is greater.
   If msHeightRatio >= msWidthRatio Then
   OLE1.Height = VScroll1.Value
   Else
   OLE1.Width = VScroll1.Value
   End If
   End Sub

6. Run the project. When the OLE object displays on the form, drag the scroll bar slider to scale the object up or down (fig. 13.6).

Fig. 13.6

After creating the sample in this section, you can zoom the size of the object by using the scroll bar.

Unfortunately, relying on the ScaleMode property settings vbOLESizeZoom and vbOLESizeStretch does not work well for all applications. In particular, Microsoft Word updates scaled objects in a peculiar way. You can work around these display problems by capturing the image of the OLE control with its Picture property. To learn how to do so, see the section "Capturing the Object's Picture," later in this chapter.

Moving and Sizing Embedded Objects during In-Place Editing

By default, Visual Basic prevents you from moving embedded objects while they are being edited in place (see fig. 13.7).

Fig. 13.7

When you try to move or resize an embedded object, it "snaps back" to its original position.

To enable users to move or size OLE objects at run time, use the ObjectMove event procedure. ObjectMove has the following form:

The arguments to the ObjectMove event procedure are the position and dimensions to which the user dragged the object. To make the object respond to the user's action, simply assign the ObjectMove arguments to the OLE control's Left, Top, Width, and Height properties, as follows:

Now when the user moves or resizes the OLE object, the OLE control adjusts to the new size and location as shown in figure 13.8.

Fig. 13.8

The ObjectMove event procedure enables the OLE control to respond when the user moves or resizes an embedded object.

Capturing the Object's Picture

The OLE control's Picture property enables you to capture the image on the control. This is useful for performance tricks and for low-level control of the object's appearance. You need to use the Picture property when the object's application doesn't update the display correctly or when you want to display the object quickly, without loading the object's application.

To see how to capture the OLE object's picture, follow these steps:

1. Create a project.
2. Add an OLE control and picture box to Form1.
3. Add the following lines of code to the form's Load event procedure. When run, these lines display the Insert Object dialog box to enable the user to select an OLE object to insert in the control. Then the code captures the OLE control's image and displays it in the picture box. The code automatically scales the image to fit in the picture box.
   Private Sub Form_Load()
   ' Use the automatic size mode to get a picture
   ' of the whole object.
   OLE1.SizeMode = vbOLESizeAutoSize
   OLE1.InsertObjDlg
   ' Capture the image of the control.
   Picture1.Picture = OLE1.Picture
   End Sub

4. Add the following lines of code to the form's Resize event procedure. These lines resize the picture control to match the size of the form.
   Private Sub Form_Resize()
   Picture1.Height = Form1.Height
   Picture1.Width = Form1.Width
   End Sub

5. Run the project. The OLE object's image displays in a picture box (fig. 13.9). Notice that when you resize the form, the image in the picture box scales up or down.

Fig. 13.9

Displaying the image of an OLE object in a picture box or image control can fix display problems with scaling. It can also speed up applications and save memory.

You can also save an OLE object's image as a Windows metafile by using the SavePicture statement, as in the following example:

By saving the images of objects to disk before exiting, you can make your OLE container applications appear to start up instantaneously—without waiting for other applications to load. For more information on this, see Chapter 14, "Advanced Programming Techniques."

Updating Linked Data

By default, Visual Basic updates linked objects whenever the user saves the source data while editing the linked object. However, Visual Basic currently does not automatically handle changes that other users make to the source file (such as storing a file on a network), nor do such changes currently trigger events in Visual Basic.

To control updating in code, use the OLE control's UpdateOptions property, the Updated event, and the Update method.

Table 13.5 describes the UpdateOptions property's three settings.

Table 13.5 The UpdateOptions Property Settings

The Updated event occurs when the user edits, closes, or renames the source file while editing the object from within the OLE control. The Updated event can also occur when the OLE control first loads (as at startup) if the source file for the link has changed since the last time that the control loaded.

These events occur regardless of the UpdateOptions property setting. However, not all applications notify the OLE control as they are updated. For instance, Microsoft Word does not trigger the Updated event until the user saves or closes the file. Microsoft Excel, however, triggers the Updated event each time that a cell of data on a worksheet changes.

The Updated event procedure has this form:

The Code argument corresponds to the type of update that occurred. Table 13.6 describes the possible values.

Table 13.6 Possible Values of the Code Argument

Some actions might cause more than one event to occur. For example, if the user closes a file and saves changes, the OLE control receives three Updated successive events: vbOLEChanged, vbOLESaved, and vbOLEClosed.

Use the Update method to update linked objects from their source files. In Visual Basic 4.0, Microsoft has changed this method, which in Visual Basic 3.0 used the Action property setting OLE_UPDATE to update links. In Visual Basic 4.0, the Action property still works, but using the Update method makes code clearer and easier to understand.

Controlling Object Activation

By default, the OLE control activates its linked or embedded object as specified by the application that provides the object (usually by double-clicking) or when the user chooses Edit or Open from the object's pop-up menu. To control activation in code, use the AutoActivate property and the DoVerb method.

Table 13.7 describes the AutoActivate property's settings.

Table 13.7 The AutoActivate Property Settings

The DoVerb method has this form:

The verbnumber argument corresponds to the index of the verb on the object's pop-up menu. If you omit this argument, the method performs the default verb (usually Edit).

Activating an Object for Editing

To activate an object for editing, use the DoVerb method with no arguments. This triggers the default response from the object's application, which is usually to edit the object.

To ensure that the action taken is Edit, check the OLE control's ObjectVerbs property. The following code checks the OLE object's list of verbs. If it finds an Edit verb, it performs it.

Opening an Object within Its Application

When you activate a linked object, it opens in its application. Embedded objects can activate in place. To ensure that an object opens in its application, use the object's Open verb.

The following code checks the OLE object's list of verbs. If the code finds an Open verb, it performs that verb.

Deactivating an Object

To deactivate an object, switch focus to another object on the form. Switching focus does not close any objects open in another application window.

When you open objects in another application window, you also must close those objects from within that application. If the application supports OLE Automation, you can close the objects programmatically through the OLE control's Object property.

The following lines of code activate an Excel object for editing. These lines open the object in the Excel application. Then the code uses the Excel Close method to close the workbook in Excel.

To use the Object property, you need special knowledge of the objects, properties, and methods that the application exposes. For instance, the following code shows how to close a Microsoft Word document. Note that this example differs significantly from the previous Excel example.

For more information on using OLE Automation methods and properties with an OLE object, see the section "Getting the OLE Automation Object from Linked or Embedded Objects" later in this chapter.

Storing and Retrieving OLE Objects

As previously mentioned, you store linked objects in separate files on disk and store embedded objects with their container. When you embed an object on a form, you store the embedded object's data with the form in your Visual Basic application.

The application that provides an OLE object supplies ways to save linked or embedded objects through the application's user interface. If your application saves its objects, you can load them into an OLE control by using the CreateEmbed or CreateLink methods. For information on using CreateEmbed and CreateLink, see the section "Creating OLE Objects at Run Time" earlier in this chapter.

To save a linked or embedded object from within code, use the OLE control's SaveToFile method. If you save OLE objects this way, their original application cannot open them directly; instead, you must open them by using the OLE control's ReadFromFile method. SaveToFile and ReadFromFile enable you to store and retrieve individual OLE objects in code.

Saving OLE Objects to Files

Use the OLE object's SaveToFile method to save a linked or embedded object directly to a file, by following these steps:

1. Open a file for binary access. The following code opens the file FOO.OLE:
   Open "foo.ole" For Binary As #1

2. Call the SaveToFile method on the OLE control that contains the object that you want to save. The following code saves the object in the oleObject control:
   oleObject.SaveToFile 1

3. Close the file. The following code line closes the file opened in step 1:
   Close 1

When you use SaveToFile, you cannot save more than one object to a particular file. The method always overwrites the entire file.

Reading OLE Objects from Files

Use the OLE object's ReadFromFile method to load a linked or embedded object directly from a file. To read an OLE object from a file, follow these steps:

1. Open the file to read for binary access. The following code opens the file FOO.OLE:
   Open "foo.ole" For Binary As #1

2. Call the ReadFromFile method on the OLE control that should display the object. The following code loads the object into the control oleObject:
   oleObject.ReadFromFile 1

3. Close the file. The following code line closes the file opened in step 1:
   Close 1

Reading a linked object that SaveToFile saved restores the link to the original file.

Getting the OLE Automation Object from Linked or Embedded Objects

Use the OLE control's Object property to get the OLE Automation object from a linked or embedded object on a form. Not all applications provide OLE Automation objects. If an object does not support OLE Automation, the Object property returns Nothing.

When working with OLE Automation objects, you should create an object variable to contain the OLE Automation object. For example, the following lines of code declare an object variable and establish a reference to an embedded worksheet when the form loads:

In the preceding example, the variable mobjExcelSheet has module-level scope; that is, other procedures in the module have access to the variable. For instance, the following Click event procedure uses the OLE Automation object mobjExcelSheet to print the embedded worksheet:

Unlike other applications that support OLE Automation, Microsoft Word requires the following special syntax to get its OLE Automation object:

You must use this special syntax because Word exposes only the WordBasic language for OLE Automation. When working with the Word OLE Automation object, remember that methods and properties apply to the current document, which might not be the one that the OLE control is currently displaying.

The following lines of code establish a reference to the WordBasic OLE Automation object:

The following two event procedures demonstrate how the WordBasic methods apply to the current document. If cmdOpenNew runs before cmdPrintDocument, Word prints the newly opened document rather than the one that the OLE control is currently displaying.

My OLE controls do not always update the display correctly.

If the control appears grayed after you edit an object, try closing the object's application. If that does not help, reload the object on the form. In extreme cases, you might have to capture the OLE object's image and display it within a picture box or image control. For information on how to do so, see the section "Capturing the Object's Picture" earlier in this chapter.

When I edit linked or embedded objects, my application crashes.

Be careful when opening linked or embedded objects for editing. Having more than one object open in the same application might cause that application to crash.

When using an OLE object's OLE Automation object, I frequently encounter "Method or property does not exist" errors.

Usually this error indicates that you are using the wrong syntax. Check the application's documentation for the method. Often the problem involves the number of arguments or their data types.

My application doesn't recognize an object that it created.

Even if your application creates an object, it doesn't recognize that object after the SaveToFile method saves it. You can load such objects only by using the ReadFromFile method.

From Here...

For more information on the following topics, see the indicated chapters:

• Chapter 14, "Advanced Programming Techniques," describes how to save OLE objects to files.
• Chapter 15, "Designing OLE Container Applications," explains how to build efficient OLE container applications.