Object Linking and Embedding (OLE)

§  What is OLE?

§  How to embed an object?

§  How to create linked object?

§  How to use OLE container control?

§  Properties and methods of OLE container control?

§  How to store binary data in Database using OLE?

§  What is OLE Drag & Drop and how to use it?

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 (as shown in figure 23.1). Your document file need not be the same as the one show in figure 23.1.

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. Please see figure 23.2.

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.

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 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 shown in figure 23.3.

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 as shown in figure 23.4.

7.      Arrange Visual Basic project and MS-Word as shown in figure 23.4.

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 figure 23.4. 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.

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.