Understanding Linking and Embedding

Before you learn how to control other applications from a VBA procedure using Automation, let's take a look at how the manual method is used to link and embed an object. Object linking and embedding, known as OLE, allows you to create compound documents. A compound document contains objects created by other applications. For example, if you embed a Word document in a Microsoft Excel worksheet, Excel only needs to know the name of the application that was used to create this object and the method of displaying the object on the screen. Compound documents are created by either linking or embedding objects. When you use the manual method to embed an object, you first need to copy it in one application and then paste it into another. The main difference between a linked object and an embedded object is in the way the object is stored and updated. Let's try this out:

1. Activate Microsoft Word and open any document.

2. Select and copy any text.

3. In a Microsoft Excel worksheet, you can now paste the copied text using one of these four methods:

The copied text will appear in the active cell (see Figure 9-2, cell A2).

■ Paste as an embedded object (choose Edit | Paste Special, click the Paste option button, and select Microsoft Word Document Object in the As list).

The text will be pasted into the worksheet as an embedded object (see Figure 9-2, cell A5). The embedded object becomes a part of the destination file. Because the embedded object is not connected with the original data, the information is static. When the data changes in the source file, the embedded object is not updated. To change the embedded data, you must double-click it. This will open the object for editing in the source program. Of course, the source program must be installed on the computer. When you embed objects, all of the data is stored in the destination file. This causes the file size to increase considerably. Notice that when you embed an object, the Formula bar displays:

=EMBED("Word.Docjment.8","")

■ Paste as a linked object (choose Edit | Paste Special, click the Paste Link option button, and select Microsoft Word Document Object in the As list).

Although the destination file displays all of the data, it stores only the address of the data. When you double-click the linked object (see Figure 9-2, cell A9), the source application is launched. Linking objects is a dynamic operation. This means that the linked data is updated automatically when the data in the source file changes. Because the destination document contains only information on how the object is linked with the source document, object linking doesn't increase the size of a destination file. The following formula is used to link an object in Microsoft Excel:

=Word.Docjment.8|'C:\vba2002\Chap09.doc'!l!0LE_LINK2l

■ Paste as a hyperlink (choose Paste | Hyperlink). The pasted data appears in the worksheet as underlined, colored text (see Figure 9-2, cell A14). You can quickly activate the source file by clicking on the hyperlink.

SI chanG9 kIs

REIH!

A

B

c

D

E

F

G

ti

1

2

Before youieam how to control other applications from a "VBA procedure using Automation,!

3

4

5

Beiorf yûu te am }low to coiï&o loiter app liratMtrj from a VBA prot e lim using rtno^iU'jii, l>t'i tit* . bçli atlKFHFjhe inaraial method is list d lo lrrri a d - tn' îc ai. ibiecL

6

7

*

3

before you learn how to control other applications from a VBA procedure us in £

10 Automation ] et' s take a 1 ook at how the

; manual method is used to link and embed an

11

object.

12

-0-

13

14

Before you learri how to control other aocllcations Horn a VBA orocedure usina Automation. let's L.

15

-

H 4

» ii \ Sheet 1 / 5heet2/îheeï3 /

M

1 M

Figure 9-2: A demonstration of linking and embedding

Figure 9-2: A demonstration of linking and embedding

0 0

Post a comment