Wednesday, 6 July 2011

Object Linking and Embedding (OLE)

What is OLE?
OLE (Object Linking and Embedding) is a means to interchange data between applications. Of late OLE has been enhanced to provide not just data but also methods that can be used by client application.

Before we proceed any further, let us understand a few terms related to OLE.

OLE Server
This is an application that can provide objects to other applications. This is also called as OLE Source application.

OLE Client
This is an application that uses objects provided by OLE Server. This is also called as OLE Container as it contains objects provided by OLE Server.

Not every application is an OLE Server. Only a few applications are capable of providing objects. In the same way not all applications are capable of receiving objects. However, there are applications, such as MS-Word and MS-Excel that are capable of being OLE source as well as OLE Container.

What is Object Embedding?
In object embedding, an object is embedded in the client application. Along with the object, client application also stores the information regarding source application (or server) that created the object. The data stored in client application is separate and no link is maintained between the data supplied by source application and data stored in client application.

The advantage with Object Embedding is, client application maintains its own copy of the data.

The disadvantage is, changes made to original data (in source application) will not be incorporated in the data maintained by client.

Whenever you double click on the object in container application, the source application will be invoked (as information regarding source application is maintained) and the data of client application is placed in source application for editing.

The following example, where we embed a few cells of Excel spreadsheet to a Word document, will make this process clear:

1. A collection of cells from a spreadsheet of MS-Excel is copied to clipboard. As MS-Excel is an OLE Server, it copies the data in the form of an object.
2. Paste the data (now in the form of an object) from Clipboard to a document in Ms-Word.
3. Now the data is embedded into MS-World document as an object. Ms-Word document contains its own copy of the data.
4. If you double click on the object in MS-Word, then an instance of MS-Excel is invoked and data from MS-Word is copied into MS-Excel.
5. User can edit embedded data using MS-Excel.
6. If user saves changes and exits MS-Excel then modified data is placed in MS-Word document.

As you have seen in the above example, once an object is embedded into MS-Word, you do not have to invoke source application manually, instead just double click on the object and that will invoke source application automatically.

However, if data is changed in the original worksheet of MS-Excel then those changes are not copied to the data in MS-Word. This is because in Object Embedding, source and container applications maintain two different copies of the data.

What is Object Linking?
Object linking makes changes made to source application available to container application. This is because container doesn’t store a separate copy of the data, instead it maintains a link to data in source application.

If you take the same example as previous one, in object linking, no separate copy of the required portion of spreadsheet is stored in MS-Word document, instead, the name of the file and the location of the data in the file are only maintained. Whenever you open MS-Word document, the data is taken from the spreadsheet file from where the data for object is taken. That is the reason why changes made in spreadsheet file (source) will be available to container application.

Advantages of Object Linking are:

• Changes made in source document are available to clients.
• As no separate copy of data is stored in clients, it saves space on disk.
Disadvantage of Object Linking is:

• If original data is lost, then client Application cannot access data.

OLE Container Control
In Visual Basic, you can embed or link an object using OLE container control. OLE control is one of the standard set of controls. This allows you to either embed or link an object either at design time or at run time thorough its properties and methods.

OLE Container control can be bound to database using a data control. We will see more about this later.

To create OLE Container control:

1. Select OLE control in Toolbox.
2. Place the control on the form with the required size.
3. As soon as OLE control is placed on the form, Insert Object Dialog is displayed to allow you to either embed or link an object into OLE Container.
4. The available options in insert dialog are - Create New, where you select an Object Type and create an object using the appropriate application, or Create From File, where you can create an object by selecting a file from file system.
To embed a word document into OLE Container control:

1. In Insert Object Dialog box select Create From File radio button.
2. Click on Browse button and select a document file .
3. Click on Ok
4. An object is embedded into OLE Container control and a part of document is displayed.
5. Run the project using F5.
6. Double click on OLE Container control. This action will invoke MS-Word and run it in OLE Container control. When OLE Server runs in OLE Client, it is called as In-Place Activation.
7. Make necessary changes using MS-Word.
8. Press ESC key to come out of In-Place activation.

Note: When you activate object, If OLE Server runs in client application, It is called as In-place Activation.
A word document in OLE container on Visual Basic form at runtime.

The followings are the other options available in Insert Object Dialog box.

Option What it does?
Create New Allows you to create a new file and then embed an object from newly created file.
Cretate From File Allows an object to be embedded or linked from a file.
Display As Icon Displays an icon instead of the content of the object.
Browse Displays a dialog box from which your can select a file.
Link If you turn on this checkbox, then object is linked, otherwise it is embedded..
Table 23:1: Options in Insert Object Dialog.


Properties of OLE container control
The following are the important properties of OLE Container Control.

Property Meaning
Class Contains the class name of the embedded object.
SizeMode See below.

FileNumber Contains the number of the file used in last saving or loading operation.
DisplayType Determines whether content of the object is displayed – 0, or an icon -1.
OLEType Contains the status of the control. Valid values are: 0- linked, 1-embedded, or 3 – none.
SourceDoc Contains the name of the file that is used to create the object.
SourceItem Specifies which part of SourceDoc is to be used to create object when you link object.
AppIsRunning Indicates whether the application that created the object is currently running.
UpdateOptions See below.
AutoActivate See below.
AutoVerbmenu Specifies whether menu containing available verbs is displayed when user clicks on right button.
OLETypeAllowed Specifes what type of object OLE container control can contain. Valid options are: 0 – only linked, 1- only embedded, and 2- both.
MiscFlags See below.
Table 23.2: Properties of OLE Container control.

SizeMode Property
Determines how image is displayed in OLE control. The following are the valid options.

Option Value Meaning
VbOLESizeClip 0 The object is displayed in orginal size. If the size of object is larger than the size of control then object is clipped to the size of the control.
VbOLESizeStretch 1 The size of the object is resized to the size of OLE container control.
VbOLESizeAutoSize 2 OLE container is automatically resized to the size of the object.
VbOLESizeZoom 3 The object is resized to fill the OLE container control as much as possible while still maintaining the original proportions (width and height) of the object.


UpdateOptions property
Specifies how a linked object is updated in Container when the orginal object is modified.

The following are the valid options for it.

Option Value Meaning
VbOLEAutomatic 0 Object is automatically updated whenever source data is updated.
VbOLEFrozen 1 Updates object whenever data in source application is saved.
VbOLEManual 2 Object is updated by invoking Update method of OLE Control.

AutoActivate property
Specifies how an object is activated. When object is activated the source application is invoked.

The following are the available options.

Option Value Meaning
VbOLEActivateManual 0 Use DoVerb method to activate object.
VbOLEActivateGetFocus 1 If object supports single click activation, then source application is activated whenever OLE container control receives focus.
VbOLEActivateDoubleclick 2 Double clicking on the object activates source application.
VbOLEActivateAuto 3 Activation depends on the type of object.



MiscFlags Property
Allows you to specify how data is to be stored and whenever in-place activation is enabled.

The following are valid values.

Option Value Meaning
VbOLEMiscFlagMemStorage
1 Causes the control to use memory to store the object while it's loaded.
VbOLEMiscFlagDisableInPlace
2 Overrides the control's default behavior of allowing in-place activation for objects that support it.


Methods of OLE Container Control
The following are important methods of OLE Container Control.

Method What it does?
PasteSpecialDlg Displays Paste Special dialog box.
Copy Copies the content of OLE container control to clipboard.
CreateEmbed Creates an embedded object with the given source document and class, if given.
CreateLink Creates a linked object with the given file and specified data in the file.
Delete Deletes the specified object and frees memory allocated to object.
InsertObjDlg Displays Insert Object Dialog box.
Paste Creates an object with the data that is in clipboard.
ReadFromFile Creates an object by loading the data that was written to the file using SaveToFile method.
SaveToFile Write the data of object to the given file. The file can be read using ReadToFile method
Close Closes the object.
DoVerb Performs the specified operation on the object.
Update Used to updates linked data manually.
Table 23.3: Methods of OLE container control.

Sample Application
Now, it is time to use OLE control for a real use. The sample application has a data entry screen that is used to take the details of a subject. The table that receives data is SUBJECTS and database name is COURSE.MDB. The fields of SUBJECTS table and their data types are listed in table 23.4.

Column Type What it stores?
Scode Text(5) Subject code.
Sdes Text(20) Subject description.
Duration Integer Number of hours.
Syllabus Binary Contains the syllabus of the subject in Word document.
Table 23.4: Columns of SUBJECTS table.

The user interface of the data entry screen

The controls on the form are bound to columns in SUBJECTS table. And some controls like command buttons are not bound to database and instead they play supporting role.

The following table will summarize the role played by each control on the form.

Control Meaning
txtScode Bound to Scode column of SUBJECTS table.
txtSdes Bound to Sdec column of SUBJECTS table.
txtDur Bound to Duration column of SUBJECTS table.
Select document command button Is used to invoke Open dialog box to allow user to select the .DOC file that contains the syllabus.
Data1 Data control that is bound to SUBJECTS table of COURSE.MDB.
OLE control Bound to Syllabus column of SUBJECTS table.
Add New Subject command button Adds a new blank record to SUBJECTS table by using AddNew method of Recordset.
Quit Command button Is used to terminate the program.
Table 23.5: Meaning of controls on the form.

Create sample application
The following are the steps to be taken to develop the sample application that takes input from user regarding details of a subject and stores the details into SUBJECT table of COURSE.MDB.

1. Start a new project using File-> New Project.
2. Select Standard EXE as the type of the project.
3. Place required controls (controls listed in table 23.5 and labels) on the form and arrange them in the manner
4. Change the following properties of the controls.

Object Property Value
Data1 DatabaseName C:\BOOKS\VB60\PROGRAMS\COURSE.MDB.
Note: The path may be different on your system.
Recordsource Subjects
Caption Subject Details
Text1 Name txtScode
Datasource Data1
Datafield Scode
Text2 Name txtSdes
DataSource Data1
DataField Sdes
Text3 Name txtdur
Datasource Data1
Datafield Duration
Ole1 Datasource Data1
DataField Syllabus
Command1 Name CmdSelDoc
Caption &Select Document…
Command2 Name CmdAddNew
Caption &Add New Subject
Command3 Name CmdQuit
Caption &Quit


Writing Code
The following is the code to be written for command buttons of the form.

Private Sub Cmdaddnew_Click()
' add a new record and allow user to enter data
Data1.Recordset.AddNew
txtScode.SetFocus ' move focus to subject code
End Sub

Private Sub CmdQuit_Click()
Unload Me
End Sub

Private Sub Cmdseldoc_Click()
With CommonDialog1
.Filter = "Word Documents|*.doc|All files|*.*"
.ShowOpen
' embed selected file into OLE control if a filename is selected
If .FileName <> "" Then
' create an embed object with the selected file
OLE1.CreateEmbed .FileName
End If
End With

End Sub
Listing 23.1: Code for command buttons on the data entry screen.


Test Run
Run the sample application to understand how it behaves. Here are the steps to be taken to test the application.

1. Run the project using F5.
2. If you already have some records in SUBJECTS table then first of those records will be displayed, otherwise you see an empty form.
3. Click on Add New Subject button.
4. When an empty form is displayed enter the following details into first three text boxes.

Scode Java
Sdes Java Language
Duration 70

5. Click on Select Document button and select the document that contains Java syllabus in Open dialog box.
6. Click on Next Record button (right arrow) in Data control to insert record into SUBJECTS table.
7. Quit program using Quit button.
8. Run the program again using F5. To test whether change is made.
9. Browse the record. You must find Java2 in the list of records.
10. If you want, you can change the document that contains the syllabus. Just click on Select Document button and supply a new document.

That’s all you have to do to put an object in your database through OLE container control. We have seen how to store a word document into database. However, it could be any binary data and not necessarily a document. For example, it could be a picture, an audio file etc.

Next we will understand what is OLE Drag and Drop.

OLE Drag and Drop
OLE Drag and Drop allows you to drag data from one control and drop it on another control. The most interesting of all is its ability to support drag and drop across applications. That means it is possible to drag some text form a document in MS-Word and drop it in a textbox in Visual Basic.

Unlike normal drag and drop, where you drag and drop one control on to another control, in OLE Drag and Drop you deal with data and not controls. In other words, what you drag and drop is data and not the control.

Although most of the Visual Basic controls support OLE Drag and Drop, the extent of support varies.

Automatic vs. Manual
When a control support automatic OLE drag, it means you need not write any code to avail the facility. And the same is the case with OLE drop. When a control supports OLE drag (or OLE Drop) but only in manual mode, then programmer has to write code to support the facility.

Some controls support both automatic OLE Drag mode and OLE Drop mode. While some other control support only automatic OLE dragging and manual dropping. Some controls support only automatic dropping and manual dragging. Some other control may support only manual OLE dragging and OLE dropping.

Note: You can know whether a control supports OLE Drag and Drop by checking whether the control has OLEDragMode and OLEDropMode properties.

Note: You can find out whether a control supports automatic OLE Dragging and OLE Dropping by seeing the setting for OLEDragMode and OLEDropMode properties respectively.


Properties, Events and Methods related to OLE Drag and Drop
The following is the list of properties, methods and events related to OLE Drag and Drop. Some of the events are fired on source and some of them are fired on target.

Properties related to OLE Drag and Drop
The following are the properties that are related to OLE Drag and Drop.

Property Meaning
OLEDragMode Determines whether to use automatic or manual dragging. This property will not be available if the control does not support automatic dragging.
OLEDropMode Specifies whether to have manual dropping or automatic dropping.
Table 23.6: Properties related to OLE Drag and Drop.

The settings for these two properties will be, None, Manual, and Automatic. However, the actual options depend upon the extent of OLE Drag and Drop support the control has.

Events related to OLE Drag and Drop
The following are the events that occur during OLE Drag and Drop process. Some of the events in the list are fired at source (where OLE Drag and Drop was initiated).

Event Meaning Occurs At
OLEDragDrop Recognizes when a source object is dropped on target. Target
OLEDragOver Recognizes when a source object is dragged over target. Target
OLEGiveFeedback Provides customized drag icon feedback to the user, based on the source object. Source
OLEStartDrag Specifies which data formats and drop effects (copy, move, or refuse data) the source supports when dragging is initiated. Source
OLESetData Provides data when the source object is dropped. This is called only when data is not passed in OLEStartDrag event. Source
OLECompleteDrag Informs the source of the action that was performed when the object was dropped into the target. Source
Table 23.7: Events related to OLE Drag and Drop.


OLEDrag Method
Starts OLE dragging. This is used when you want to manually start OLE dragging.


Dragging text from MS-Word into a text box using OLE Drag and Drop

Let us now understand the process involved in dragging text entered in MS-Word into a textbox that is placed on a form in Visual Basic.

1. Start a new project using File-> New Project -> Standard EXE.
2. Place a Textbox on the form.
3. Change OLEDropMode property of the textbox (Text1) to 1-Automatic.
4. Run Visual Basic project using F5.
5. Start MS-Word.
6. Type some text in word document
7. Arrange Visual Basic project and MS-Word
8. Select the text in word document and drag the text while holding down ctrl key into textbox of the form.

Note: While you are dragging, if you hold down the CTRL key then the data is copied to target, otherwise it is moved.

The result of the above process could be seen in . Textbox in the form contains the text that is typed in word document. As we held down CTRL key during drag and drop, it was copied otherwise it would have been moved from MS-Word to textbox.
Textbox after text is dragged from word document and dropped into it.


Let us now develop a small application to understand how to handle OLE Drag and Drop manually.

Sample Application for Manual OLE Drag and Drop
The sample application that we are going to develop has a simple form with two textboxes. First textbox is used as the source and second textbox is used as the target.

The sample application demonstrates how to use manual dragging and manual dropping. When user drags text from first textbox to second textbox, the text is to be moved to second textbox after it is converted to upper Case. In this, after dropping is complete, target informs to source that the text is to be moved. Then first textbox (source) removes data from the textbox.

The main purpose of the application is to let you understand the steps involved handling OLE Drag and Drop manually.

First textbox's OLEDragMode is set to Manual and OLEDropMode property is set to Automatic.

Second textbox's OLEDragMode is set to Automatic and OLEDropMode is set to Manual.

The following are the steps to be taken to develop this application.

1. Start a new project using File-> New Project and select Standard EXE as the type of the project.
2. Place two textboxes along with labels and a command button.
3. Arrange the controls on the form in a neat format.
4. Change the following properties.
Object Property Value
Label1 Caption Source
Text1 Name txtsource
Text ""
OLEDragMode 0-Manual
OLEDropMode 2-Automatic.
Text2 Name txtTarget
Text ""
OLEDragMode 1-Automatic
OLEDropMode 1-Manual
Form Caption OLE Drag and Drop
Command1 Name CmdQuit
Caption &Quit

Writing code
Let us now understand what are the important operations in the sample application and write the code for those events. Please refer to table 23.7 for the list of events and their meaning.

• Start OLE Dragging when mouse button is down (MouseDown event) on txtSource
• When OLEDrag method is used to initiate OLE Dragging then OLEStartDrag event for source occurs. The data that is to be dragged is copied into DataObject using SetData method of DataObject.
• When data is dropped on txtTarget, then OLEDragDrop event occurs for txtTarget. Then data is sought from source using GetData method of DataObject.
• Text is removed from the txtSource in OLECompleteDrag event, if operation is move.

Private Sub txtsource_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' start OLE dragging
txtsource.OLEDrag
End Sub

Private Sub txtsource_OLECompleteDrag(Effect As Long)
If (Effect and vbDropEffectMove)<>0 Then ‘ bitwise Anding
txtsource.Text = ""
End If
End Sub

Private Sub txtsource_OLEStartDrag(Data As DataObject, AllowedEffects As Long)
' set allowed effects
AllowedEffects = vbDropEffectMove Or vbDropEffectCopy
Data.SetData UCase(txtsource.Text)
End Sub

Private Sub txtTarget_OLEDragDrop(Data As DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)

txtTarget.Text = Data.GetData(vbCFText)
Effect = vbDropEffectMove

End Sub
Listing 23.2: Code for OLE Drag and Drop sample form.

Let us now understand the code shown in listing 23.2.First let us understand DataObject, which is passed as parameter of events related to OLE Drag and Drop.

DataObject object
This is the means through which data is moved from source control to target control. It has the following properties and methods.

Type Name Meaning
Property Files Contains the names of the files being dragged from windows explorer.
Method Clear Clears the content of the DataObject.
GetData Retrieves the data from DataObject.
SetData Places the given data in the DataObject or specifies the type of data that will be made available upon request.
GetFormat Determines if the specified DataObject format is available in the DataObject.
Table 23.8: Properties and Methods of DataObject.

AllowedEffects parameter of OLEStartDrag event
Specifies the effects that are supported by the source object. The following are the available settings for this.

0 - None, Target cannot accept the data.
1 - Copy, dropping results in copying the data from source to target.
2 - Move, dropping results in moving the data from source to target.

Note: You can use bitwise OR operator to combine more than one setting

Note: OLEStartDrag event occurs even if object's OLEDragMode property is set to Automatic.

Effect parameter of OLEDragDrop Event
Allows the target object to specify to source object the action it has taken on the data. For valid values, please see on-line documentation.

Effect parameter of OLECompleDrag Event
Informs the source object the action that has been performed on the target object.

Note: If you are interested in exploring OLE Drag and Drop, read "OLE Drag and Drop" in "Responding to Mouse and Keyboard events" chapter of "Programmer's Guide".

OLE Drag and Drop is an exciting topic. There are so many interesting things that you can do with this. Go on and explore it. In the next chapter, we will discuss about class modules and collections.