PART III Share, Publish, and Present Data
CHAPTER 16 Use Excel with the Other Office Applications
Color profile: Generic CMYK printer profile Composite Default screen
How to…
■ Transfer data using the Clipboard and Office Clipboard
■ Embed and link objects
■ Insert Excel objects in Word documents
■ Insert Excel objects in PowerPoint presentations
■ Insert Word objects in worksheets
■ Insert PowerPoint objects in worksheets
As its full name suggests, Office Excel 2003 is thoroughly integrated with the other applications in Office—Office Word, Office PowerPoint, Office Outlook, and Office Access. In this chapter, you’ll learn how to make Excel share data with and receive data from the other Office applications, focusing mainly on Word and PowerPoint.
The primary tools for passing information from one application to another are the Windows Clipboard and the Office Clipboard. For example, you can copy cells from an Excel worksheet and paste them into a table in a Word document or onto a PowerPoint slide. Similarly, you can copy data from a Word document or an Outlook message and paste it into an Excel worksheet.
You can also use the Clipboard and the Office Clipboard to embed or link data from a file created in one application in a file created in another application. For greater control over the objects you embed and link, you can use the Object dialog box.
Because you’re likely to want to transfer data both to and from Excel, this chapter discusses not just Excel but also the other Office applications to some extent. It discusses the methods for transferring, embedding, and linking data in general, and then gives specific examples of integrating Excel with Word and PowerPoint.
For heavier-duty data sharing in a corporate environment, you can use Excel’s XML features to manipulate XML data files and to save data in a machine-readable format, as discussed in Chapter 15.
Transfer Data Using the Clipboard and Office Clipboard
As you saw in Chapter 3, the Windows Clipboard and the Office Clipboard provide an easy means of copying and moving data, either within an application or between applications. From the source application, you issue a Copy command or a Cut command to place the appropriate data on the Windows Clipboard or the Office Clipboard, then switch to the destination file in the destination application and issue a Paste command or Paste Special command to insert the information.
These are the main points you need to remember when transferring data via the Windows Clipboard and the Office Clipboard:
■ The Windows Clipboard can hold several different types of data, including text and graphics, but it can hold only one item of each type at once. When you issue another Cut command
16
or Copy command, Windows overwrites the contents of the Clipboard for that data type with the new information.
■ The Office Clipboard can contain up to 24 items of the same type or of different types.
You can display the Office Clipboard task pane at any time by choosing Edit | Office Clipboard.
■ You can use the Paste Special dialog box to control the format in which the object is pasted.
■ You can also simply issue a Paste command to paste the object in the default format. (The default format varies depending on the type of object you’re pasting and the destination application into which you’re pasting it.) If you don’t get the result you want, you can use the Paste Options Smart Tag to change the format in which the object was pasted.
(Alternatively, you can undo the Paste operation and then use the Paste Special dialog box instead.)
Embed and Link Objects
Excel and the other Office applications support three different ways of including an object created in one application in a file created in another application: embedding, linking, and inserting. An objectis a component of a file that can be handled separately. Examples of objects include charts and ranges in Excel, tables in Word, and slides in PowerPoint. Embedding, linking, and inserting are different ways of including an object created in one application in a file created in another application.
You’ll read about embedding and linking at some length in this chapter. Inserting is relatively straightforward, and if you’ve worked your way through this book, you’ll already have inserted objects such as graphics (Chapter 5) in your worksheets. When you insert an object in a file, the file contains neither the information for editing the object in place nor a link to the source file that contains the object: the object simply appears in the file in the place you specify. Graphics are typically inserted in another file (for example, a document, workbook, or presentation) rather than being embedded or linked.
Before using embedding or linking, you should understand the differences between the two, the effects they produce, and know when to use which technique.
Understand the Differences Between Embedding and Linking
Embeddingis the basic means of inserting an object created in another application into a file. For example, if you need to create slides that contain charts or WordArt objects, you use embedding.
When you embed an object in a file, the file contains a full copy of that object. For example, if you embed an Excel chart in a Word document, that document contains a full copy of the chart together with the workbook that contains it. Depending on the type of object involved, embedding can greatly increase the file size.
The copy is independent of the original chart in the Excel workbook, and you can edit it separately. You can’t update the copy directly from the original chart. Instead, you can replace
CHAPTER 16: Use Excel with the Other Office Applications 347
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 16 Color profile: Generic CMYK printer profile
Composite Default screen
clumsy to make sense in most cases, but for some purposes (for example, version control of documentation) it can sometimes prove a better option than linking.
Linkingis the more complex method of inserting an object created in another application into a file. When you link an object to a file, the file displays the current information for that object but stores only a link that describes the object, where it’s located, and other relevant information.
Storing the information about the link is much more compact than storing the actual data for even the smallest object, so the size of the file that contains the link hardly changes. When you need to edit a linked object, you do so at the source.
When you link an object, you can update the link by issuing an Update command. The application reads the latest data from the source of the link and displays it in the file. However, the application can’t update the link if either the source or the destination is offline relative to the other, or if the source file has moved or been renamed so that the application and Windows can’t identify it. (The applications and Windows are now better at identifying renamed files successfully than they used to be in the past, but you may still be able to confuse them.)
Understand the Advantages and Disadvantages of Embedding and Linking
The advantage of embedding is that, because the object is saved in the file, the object remains available even if you move the file or disconnect the computer so that the object's source file is no longer available. The disadvantages are that embedding an object significantly increases the file’s size (because the object’s data must be saved in it, either in the original format or in a modified format) and that there’s no easy way to update the object if the source file changes:
instead, you need to manually replace the embedded object with the latest version of the object from the source file.
Linking has two advantages. First, because only the link is saved in the file, not the object itself, the file’s size increases by only a tiny amount. Linking can greatly reduce the file size of a file that includes many large or complex objects. Second, you can make the file display the latest version of the object by updating the link.
The disadvantage of linking is that if the source file isn’t available, the object doesn’t appear.
So if, for example, you need to distribute a worksheet that included PowerPoint slides, embedding would be a better choice than linking, even though the file size of the workbook with the embedded slides would be far larger than that of the workbook with links to those same slides.
Choose When to Embed and When to Link
To decide whether to embed or link objects, consider the following:
■ Will you need to edit the object in the destination file? If so, embed it.
■ Do you need to keep file size down? If so, link the objects.
16
CHAPTER 16: Use Excel with the Other Office Applications 349
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 16
■ Will the destination file and the source files stay in the same place as when you create the destination file, or do the files need to be able to move independently of each other?
If you need to be able to move the destination file to another computer that won’t be able to access the source files, embed the objects rather than link them.
■ Will different people need to work on different components of the same project at the same time? Even with Excel’s support for a single file to be opened for editing by multiple people at the same time, it’s best to keep shared editing to a minimum (or avoid it altogether).
By linking objects rather than embedding them, you can enable different people to work on different components without the possibility of confusion or corruption. For example, you might continue to hack at the Word report while Annie polished the slides linked to it and Bill hammered the latest data in the Excel spreadsheet that provides the linked charts.
Verify Whether an Object Is Linked or Embedded
By looking at an object in a document, you can’t immediately tell whether it’s linked or embedded.
The easiest way to find out in Excel is to select the object and check the readout in the reference area. If the readout starts with=EMBED(for example, =EMBED("Word.Document.8","")), the object is embedded. If the readout contains a reference to a file by name (for example,
=Word.Document.8|'C:\Temp\Doc1.doc'!'!OLE_LINK1'), the object is linked.
In Word, PowerPoint, and Outlook, right-click the object and see whether the shortcut menu contains an Update Link command. If so, the object is linked; if not, the object is embedded.
Embed or Link an Object
You can embed or link an object by using the Paste Special dialog box or the Object dialog box.
In some cases, you can also choose to display the embedded or linked object as an icon rather than as itself.
Embed or Link an Object by Using the Paste Special Dialog Box
In most cases, the easiest way to embed or link an existing object is to use the Paste Special dialog box. Follow these steps:
1. In the object’s source application, select the object and issue a Copy command (for example, pressCTRL-Cor click the Copy button on the Standard toolbar).
2. Activate the destination application and select the location in which you want to embed or link the object.
Color profile: Generic CMYK printer profile Composite Default screen
3. Choose Edit | Paste Special to display the Paste Special dialog box. This illustration shows the Paste Special dialog box for Excel with a Word object (a table) on the Clipboard:
4. Choose the format in which you want to embed or link the object. The choices available depend on the type of object you copied and the destination application.
5. Select the Paste option button to embed the object. Select the Paste Link option button to link the object.
6. If the Display As Icon check box is available, you can select it to make the application display not the object itself but an icon representing it. See “Display an Embedded or Linked Object As an Icon,” later in this chapter, for a discussion of why you may want to do this and how the icon appears.
7. Click the OK button to close the Paste Special dialog box. The application embeds or links the object, depending on the choice you made.
Embed or Link an Object by Using the Object Dialog Box
You can also embed a new object that you create and embed in the same process. To do so, follow these steps:
1. In the destination application, choose Insert | Object to display the Object dialog box (Figure 16-1). (In PowerPoint, the dialog box is called Insert Object and is configured a little differently than the Object dialog box.)
2. Click the Create New tab if it isn’t already displayed.
3. Select the type of object you want to create and embed.
4. Click the OK button to close the Object dialog box and insert the object.
16
By using the Create from File tab of the Object dialog box, you can embed or link an object that consists of the entire contents of an already existing file. To do so, follow these steps:
1. In the destination application, choose Insert | Object to display the Object dialog box.
2. Click the Create from File tab (Figure 16-2) if it isn’t already displayed.
3. Enter the path and file name in the File Name text box. (The easiest way to enter this is to click the Browse button, use the Browse dialog box to navigate to and select the file, and then click the OK button.)
4. Select the Link to File check box if you want to link the object rather than embed it.
5. Select the Display As Icon check box (if it’s available) if you want to display an icon instead of the object itself. See “Display an Embedded or Linked Object As an Icon,”
next, for a discussion of why you may want to do this and how the icon appears.
6. Click the OK button to close the Object dialog box and link or embed the object.
Display an Embedded or Linked Object As an Icon
Instead of embedding or linking an object so that it is displayed, you can sometimes make the object appear as an icon. Displaying an object as an icon is available only for some paste and paste-link formats.
Displaying the icon can be useful when you want to make extra information available to the user of a file but you don’t want that information to overshadow the file’s primary content. For
CHAPTER 16: Use Excel with the Other Office Applications 351
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 16
FIGURE 16-1 You can create and embed a new object from the Create New tab of the Object dialog box.
Color profile: Generic CMYK printer profile Composite Default screen
slide. So instead, you might choose to display an icon that lets the user open the worksheet in a separate window where they can examine it comfortably.
To display an object as an icon, select the Display As Icon check box in the Paste Special dialog box or the Object dialog box. When you select this check box, the Paste Special dialog box or the Object dialog box displays the current icon and caption for the object, together with the Change Icon button:
To change the icon or caption, click the Change Icon button, use the options in the Change Icon dialog box (shown here) to specify the icon or the caption, and then click the OK button.
FIGURE 16-2 You can embed or link an existing object from the Create from File tab of the Object dialog box.
16
Most applications have a limited selection of icons, but Windows library files such as MORICONS.DLL and SHELL32.DLL (in the System32 folder in your %Windir%
folder—for example, the Windows folder) offer some colorful and entertaining icons.
You may also have icons of your own that you prefer to use.
Edit an Embedded Object
You edit an embedded object “in place”—in its location in the destination file. The easiest way to start the editing is to double-click the object, but you can also right-click it and issue an Edit command from the object’s submenu. For example, right-click an embedded PowerPoint slide and choose Slide Object | Edit from the shortcut menu.
When you issue an Edit command in either of these ways, the application displays a thick shaded border around the object and replaces its own menus and toolbars with those of the application that created the object. For example, Figure 16-3 shows an embedded Excel chart being edited in a Word document. Word is displaying the Excel menus and toolbars. You can then edit the object as if you were working in the other application (which, in effect, you are).
The source object remains unchanged, because there’s no link between the embedded object and the source.
For you to be able to edit an embedded object, the application that created the object must be installed on the computer you’re using. This can cause problems when you move a document to a different computer. For example, suppose you create a Word document that contains a couple of Excel charts on your work computer. If you take this document home and open it on your home computer, which has Word and Microsoft Works installed, you’ll be able to edit the Word parts of the document but not the embedded Excel objects.
Edit a Linked Object
You edit a linked object in its source application rather than in place in the destination application.
Right-click the object and issue an Edit command (for example, choose Document Object | Edit for a Word document object) from the shortcut menu to open the object for editing in the source application. You can then edit the object as usual. When you close the object in the source
CHAPTER 16: Use Excel with the Other Office Applications 353
HowTo-Tght (8)/ How to Do Everything with Microsoft Office Excel 2003 / Hart-Davis / 3071-1 / Chapter 16 Color profile: Generic CMYK printer profile
Composite Default screen
Edit, Update, and Break Links
To work with links in a file, choose Edit | Links and work in the Edit Links dialog box (Figure 16-4).
From this dialog box, you can take these actions to a selected link:
■ Click the Update Values button to force an update of the link.
■ Click the Change Source button and use the resulting Change Links dialog box to change the link to a different file:
FIGURE 16-3 Double-click an embedded object to edit it in place in the file that contains it.