TOC BACK FORWARD HOME

Microsoft® Excel 97 Quick Reference

- 9 -
Linking and Embedding

Object Linking and Embedding (OLE) enables you to create work in one application and share that work with another application. You can use the numbers in a worksheet to create a table in a printed report; import a database table for use in Excel; or even combine the contents of many kinds of documents, in many different applications, to create an annual report. Object linking and object embedding are two related techniques that seem to accomplish similar goals. The main difference between linking and embedding is where the data is stored.

Linked data is stored in a source file. When you link an object, a copy of the original data appears in the second (destination) application; the original data remains intact in the first (source) application. If you use object linking to copy a table of numbers from Excel to Word, for example, the data remains in Excel, but a copy of the table also appears in Word. To change the table in Word, you return to Excel and change the original numbers. Because a link has been set up between the two applications, any changes to the data in Excel change the table in Word.

Embedded objects become part of the destination document. When you embed an object, you create the object in the source application as you do when linking; however, there is only one copy of the object and that resides within the destination document. You can embed an Excel chart in a Word document. In Word, you create the object using Excel menus and tools. You then update the object and exit Excel. When you want to edit the embedded object, you double-click the chart in Word to open the Excel window in which the object resides.

In OLE 2, which is a more recent version of OLE, you also can drag and drop shared data between open applications. OLE 2 makes it easier to edit the objects you link and embed. Suppose you create a worksheet in Excel that you embed into a Word report. When you edit the worksheet from Word, OLE 2 provides appropriate Excel menus that enable you to edit the worksheet more quickly and easily than if you switch to the actual source program. Many of the applications you use in Windows 95, such as Excel 97 and Word 97, use OLE 2.

NOTE: The general techniques you learn in this section apply to other Microsoft Office applications (such as Word, PowerPoint, and Access) as well as Windows applications from other software developers (such as Excel, WordPerfect, and Approach) that support OLE. Consult the user manual for the application to see whether it is OLE-compatible.

Cell Pictures: Capturing a Picture of Cells

If you want to create a worksheet displaying ranges of data from multiple worksheets, you can capture a picture of the ranges from each worksheet and combine them onto one worksheet as linked objects. You also can use these captured cell pictures in other Office applications, such as Microsoft Word.

Capturing cell pictures provides several advantages over creating formula links to cells in other worksheets. The linked cell pictures can be opened and updated quickly--you can double-click the cell picture to access and modify the source information. Linked cell pictures are actually objects that can easily be moved or resized, which facilitates layout. In addition, you can format linked cell pictures by using many of the same features as text boxes.

The cell picture includes only the visible information in the captured cells, including cell gridlines. You may want to omit the gridlines before you capture the picture (see "Hiding: Gridlines" in the "Customizing" section). You also may want to resize columns or rows before capturing the cell picture in order to improve the appearance of the linked picture (see "Adjusting: Column Widths" and "Adjusting: Row Heights" in the "Editing Workbooks" section).

Steps

  1. In the source worksheet, select the range of cells you want to create a picture from, and click the Copy button on the Standard toolbar.

  2. Select the destination worksheet (or other document) where you want to paste the picture.

  3. Hold down the Shift button, then choose Edit, Paste Picture Link. The pasted cells now appear as an object in the destination worksheet.

  4. Move or size the object, as desired.

TIP: If you want to copy the picture to a document in another Office application, copy the Excel range to the Clipboard; then choose Edit, Paste Special in the other application. Click Paste Link; then in the As list box, select Picture. Click OK.

Cell Pictures: Updating Linked Cell Pictures

You can update information in the source worksheet that contains the data linked to a cell picture, and have those changes reflected immediately in the cell picture. You don't need to remember where the source data is located, because you can activate the source worksheet directly from the cell picture. This feature is especially convenient if you need to update several cell pictures contained in the same worksheet. (See "Cell Pictures: Capturing a Picture of Cells" before you complete this task.)

Steps

  1. Open the worksheet (or other Office document) containing the cell picture; then double-click the picture. Excel opens the source worksheet and selects the range that is linked to the picture.

  2. Make any desired changes to the source worksheet, and then save the source worksheet. The changes are reflected in the linked cell picture.

Dragging Data between Programs

You can use the mouse to move or copy information from one application to another using a simple drag-and-drop operation, as long as both applications support OLE.

Steps

  1. Open both the source and destination applications; position the applications so that you can see the area you want to copy from and the area you want to paste to.

  2. Select the data in the source application that you want to move or copy to the destination application.

  3. To move the selection, drag it to the destination application and drop it where you want the data to appear. To copy the selection, hold down the Ctrl key while you drag and drop.

TIP: To more easily arrange the two applications on-screen, minimize all applications except the two you are working with. Right-click in the Windows taskbar and choose either Tile Horizontally or Tile Vertically.

Embedding: Editing Linked and Embedded Objects

When you link or embed an object in an Excel worksheet, you can edit the object or the linked data using the data's original application. This enables you to use features designed for this specific type of data. You can edit a linked object by changing and then updating the original object or by editing the link itself.

Objects linked into Excel update automatically, by default. When you change the source document and save the file, the object embedded in Excel updates to reflect the change.

Steps

  1. To edit the object, double-click it. The source application reopens, with the object on-screen.

  2. Edit the object, as desired.

  3. Click outside the source application when you are done, to return to the worksheet and restore the Excel menus and toolbars.

TIP: To quickly access all the commands that apply to an embedded object, right-click the object to display the object's shortcut menu.

Embedding: Including Data from Other Applications

After you embed data from another application into Excel, the data is part of the Excel worksheet. You can edit the embedded data by starting the other application from within Excel. You can embed an object into an Excel document in two ways: by using the Insert command and creating the embedded object from within Excel; or by opening an application that contains an existing object, and then copying and pasting the object into Excel. Objects you embed in Excel appear as pictures that you can resize and move.

Steps

  1. To insert a new embedded object into Excel, open the worksheet where you want the object to appear and choose Insert, Object; then click the Create New tab.

  2. In the Object Type list, select the source application you want to use; then click OK. The source application opens.

  3. Create the object, using the source application; then, click outside of the source application area.

  4. If a confirmation dialog box appears, click Yes.

Linking: Cells by Pointing

If you need to create links to individual cells or links within formulas, you can use the pointing method to create the links. You can point to cells in other workbooks and create the links, just as you can point to any cell in the current workbook. To point to a cell so that it is included in a formula, click it as you build the formula.

Steps

  1. Open the destination and source workbooks; then activate the destination workbook.

  2. Select the cell that you want to contain the link and start the formula. The formula can include several terms or can just use an equal sign (=) and the single linked cell.

  3. Activate the source workbook; then select the source cell or range that supplies data to the link.

  4. Continue building the formula. After you complete the formula, press Enter.

(See also "Formulas: Entering Formulas" in the "Formula & Function Management" section.)

Linking: Cells with Copy and Paste Link

You can link an Excel cell or range in a source workbook to a cell or range in a destination workbook by using the Copy and Paste Link commands. The Paste Special dialog box used in this procedure enables you to choose which components of a cell or range you want to paste link, such as all, formulas, values, or formats. You also can specify a mathematical operation to perform on the link, if desired.

Steps

  1. Open the destination and source workbooks; then activate the source workbook.

  2. Select the range of cells that provide the data you want linked; then choose Edit, Copy.

  3. Activate the destination workbook; then click the top-left cell of the range where you want the link to appear.

  4. Choose Edit, Paste Special. The Paste Special dialog box appears.

  5. In the Paste area, select All; in the Operation area, select None. Then click the Paste Link button.

Linking: Excel Data with Other Windows Applications

Through object linking, a Windows application can send data to or receive data from another Windows application that is capable of OLE. Excel can create links by using its Edit, Copy and Paste Special commands as long as the other Windows application also has OLE commands available. In this case, creating object links between two Windows applications is as easy as linking two worksheets.

Steps

  1. Open Excel and the other Windows application; then display the source application.

  2. Select the text, cell, range, value, graphic object, or data that you want to link.

  3. Choose Edit, Copy.

  4. Switch to the destination application and select where you want the linked data to appear.

  5. Choose Edit, Paste Special; then select Paste Link. Click OK.

NOTE: If the Paste Link option is not available in the Edit menu after you copy data from another Windows application to Excel, the source application doesn't support OLE through menus; you cannot paste the link into Excel.

Linking: Opening Linked Workbooks

When you open a workbook containing linked data, you update the linked data in different ways. If the source workbook is already open, the destination workbook automatically updates when you open it. If the source workbook is not open when you open the destination workbook, you can choose to keep the old values or update links to files on disk.

Steps

  1. Open the destination workbook that contains the links, and choose Edit, Links.

  2. In the Source File list, select the files you want to open. To select multiple files, hold down the Ctrl key and click each file. Unopened files appear with their path name.

  3. To complete the update process, click the Open Source button.

NOTE: Be sure that the destination workbook is the active workbook. If a workbook without links is active, the Edit, Links command is unavailable.

Linking: Updating and Changing Linked Workbooks

To maintain linked workbooks, you need to know how to re-establish lost links and how to update links. If you rename or move source workbooks to different folders, destination workbooks cannot automatically find the new location of the source data. These links must be reestablished.

Steps

  1. Open the destination workbook that contains the links; then choose Edit, Links.

  2. In the Source File list, select the files you want to update or change. Unopened files appear with their path name. Click the Change Source button; the current link is displayed at the top of the Change Links dialog box.

  3. Select a folder and file name for the new supporting workbook, or type the folder and file name of the file you want to establish as the source.

  4. Click OK to link to the file name you selected, or click Cancel to ignore the change.

  5. If you selected multiple source files in step 2 above, repeat steps 3 and 4 for each workbook.

TIP: To update an active destination workbook if the source workbook is unopened, choose Edit, Links. Then, select the source workbook and click the Update Now button.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.