The applications in Microsoft Office are designed to work together. These programs have a common look and feel, and sharing data among these applications is usually quite easy. This chapter explores some ways in which you can make use of other applications while working with Excel, as well as some ways in which you can use Excel while working with other applications.
Excel can import and export a variety of different file types. Besides sharing data using files, you can also transfer data to and from other open Windows applications in several other ways:
Copy and paste, using either the Windows Clipboard or the Office Clipboard. Copying and pasting information creates a static copy of the data.
Create a link so that changes in the source data are reflected in the destination document.
Embed an entire object from one application into another application’s document.
This chapter discusses these techniques and shows you how to use them.
Copying information from one Windows application to another is quite easy. The application that contains the information that you’re copying is called the source application, and the application to which you’re copying the information is called the destination application.
Following are the general steps that are required to copy from one application to another.
Activate the source document window that contains the information that you want to copy.
Select the information by using the mouse or the keyboard.
If the source application is a Microsoft Office 2007 application, choose Home
Clipboard Copy. Most other applications have an Edit Copy menu command. In most cases, pressing Ctrl+C copies the selection to the Clipboard.Activate the destination application. If the program isn’t running, you can start it without affecting the contents of the clipboard.
Move to the appropriate position in the destination application (where you want to paste the copied material).
If the destination application is a Microsoft Office 2007 application, choose Home
Clipboard Paste. Most other applications have an Edit Paste command, and you can usually use Ctrl+V to paste. If the Clipboard contents aren’t appropriate for pasting, the Paste command is grayed (not available). You can sometimes select other paste options by choosing Home Clipboard Paste (or Edit Paste Special).
In Step 3 in the preceding steps, you also can choose Home Clipboard Cut (or Edit Cut) from the source application menu. This step deletes your selection from the source application after placing the selection on the Clipboard.
One of the most frequently used software combinations is a spreadsheet and a word processor. This section describes some of the ways to copy information from an Excel worksheet to a Word document.
Generally speaking, you can copy something from Excel and paste it into Word in one of two ways:
As static information: If the Excel data changes, the changes aren’t reflected in the Word document.
As a link: If the Excel data changes, the changes are shown in the Word document.
You can find all the various paste options for Word in the Paste Special dialog box, which appears when you choose Home Clipboard Paste Paste Special.
Note
If you use Word’s standard paste command (Home Clipboard Paste, or Ctrl+V), you’ll find that the paste method varies, depending on what is pasted. An Excel range copied and pasted to Word is pasted as a static table. An Excel chart, on the other hand, is pasted as a link.
Often, you don’t need a link when you copy data from Excel to Word. For example, if you’re preparing a report in your word processor and you simply want to include a range of data from an Excel worksheet, you probably don’t need to create a link (unless the data in the Excel worksheet may be changed). After you’ve copied an Excel range, activate Word and choose Home Clipboard Paste or press Ctrl+V. The range appears as a Word table, and is not linked to the Excel workbook.
For more control over pasting, use the Paste Special dialog box. Figure 29.1 shows Word’s Paste Special dialog box when you’ve copied an Excel range and chosen the Paste option. If you select one of the choices in the Paste Special dialog box with the Paste option selected, the data is pasted without creating a link.
The paste options in Word’s Paste Special dialog box when a range is copied include
Microsoft Office Excel Worksheet Object: You can edit this object with Excel. Double-click, and Word’s Ribbon is replaced with Excel’s Ribbon. See “Embedding an Excel range in a Word document,” later in this chapter.
Formatted Text (RTF): The range is pasted as a table, with some formatting retained.
Unformatted Text: Only the raw information is pasted, with no formatting. Cells are separated with a Tab character.
Picture (Windows Metafile): The range is pasted as a picture.
Bitmap: The range is pasted as a picture.
Picture (Enhanced Metafile): The range is pasted as a picture.
HTML Format: The range is pasted as a table, with all formatting retained. This format is pasted when you choose Home
Clipboard Paste.Unformatted Unicode Text: Only the raw information is pasted, with no formatting. Cells are separated with a Tab character.
If you’ve copied an Excel chart to the Clipboard, Word’s Paste Special dialog box displays different options. Figure 29.2 shows Word’s Paste Special dialog box when an Excel chart is copied. The options are
Microsoft Office Excel Worksheet Object: You can edit this object with Excel. Double-click, and Word’s Ribbon is replaced with Excel’s Ribbon.
Picture (Windows Metafile): The chart is pasted as a picture.
Bitmap: The chart is pasted as bitmap image.
Picture (Enhanced Metafile): The chart is pasted as a picture.
Picture (GIF): The chart is pasted as a GIF image.
Picture (PNG): The chart is pasted as a PNG (Portable Network Graphics) image.
Picture (JPEG): The chart is pasted as a JPEG image, which usually results in a fuzzy image.
Microsoft Office Graphic Object: The image is linked to the Excel range, and you can also edit it in Word. This format is pasted when you choose Home
Clipboard Paste.
If the data that you’re copying will change, you may want to paste a link. When would you want to use this technique? If you generate proposals using Word, for example, you may need to refer to pricing information that you store in an Excel worksheet. If you set up a link between your Word document and the Excel worksheet, you can be sure that your proposals always quote the latest prices.
If you paste the data by using the Paste Link option in the Paste Special dialog box, you can make changes to the source document, and those changes appear in the destination application. You can test these changes by displaying both applications on-screen, making changes to the source document, and watching for them to appear in the destination document.
Caution
You can break links rather easily. If you move the source document to another directory or save it under a different name, for example, the destination document’s application isn’t able to update the link. In such a case, you need to re-establish the link manually.
Figure 29.3 shows Word’s Paste Special dialog box when an Excel range has been copied and the Paste Link option is specified. Note that, with one exception, these options are the same ones available when you select the Paste option. The only format that isn’t available for pasting a link is Picture (Enhanced Metafile).
When an Excel chart is on the Clipboard, you can also choose the Paste Link option in Word’s Paste Special dialog box.
Note
When you paste an Excel chart to Word using the Microsoft Office Graphic Object option, only the chart’s data is linked. All other modifications (such as formatting or changing the chart type) aren’t reflected in the copy pasted in the Word document. When you activate the chart in Word, you can use the Chart Tools context menu to make changes to the chart.
To edit (or break) a link, choose Office Prepare Edit Links To Files in Word, which displays the Links dialog box shown in Figure 29.4. Select the Source File from the list, and click the Break Link button. After breaking a link, the data remains in the destination document, but it’s no longer linked to the source document.
Using Object Linking and Embedding (OLE), you can also embed an object to share information between Windows applications. This technique enables you to insert an object from another program and use that program’s editing tools to manipulate it. The OLE objects can be such items as
Text documents from other products, such as word processors
Drawings or pictures from other products
Information from special OLE server applications, such as Microsoft Equation
Sound files
Video or animation files
Many (but certainly not all) Windows applications support OLE. Embedding is often used for a document that you will distribute to others. It can eliminate the need to send multiple document files and help avoid broken link problems.
To embed an object into an Excel workbook, choose Insert Text Object, which displays the Object dialog box. This dialog box has two tabs, one for creating a new object and one for creating an object from an existing file.
To embed an empty Word document into an Excel worksheet, choose Insert Text Object in Excel. In the Object dialog box, click the Create New tab and select Microsoft Office Word Document from the Object type list.
The result is a blank Word document, activated and ready for you to enter text. Notice that Word’s Ribbon replaces Excel’s Ribbon, giving you access to all Word’s features.
To embed a copy of an existing Word file, use the Create From File tab in the Object dialog box and then locate the file on your hard drive. The Word document is inserted into your Excel worksheet. Double-click the document to display Word’s Ribbon.
You can embed many other types of objects, including audio clips, video clips, MIDI sequences, and even an entire Microsoft PowerPoint presentation.
Figure 29.5 shows an MP3 audio file embedded in a worksheet. Clicking the object plays the song on the default MP3 players.
Microsoft Office includes several additional applications that you may find useful. For example, you can embed a Microsoft Equation object in an Excel document to graphically illustrate a formula that you use in a worksheet.
Tip
Some of the object types listed in the Object dialog box can result in quite useful and interesting items when inserted into an Excel worksheet. If you’re not sure what an object type is, try adding the object to a blank Excel workbook to see what is available. Keep in mind that not all the objects listed in this dialog box actually work with Excel. Attempting to use some of them may even crash Excel.
You can embed an Excel workbook in a Word document in three ways:
Copy a range and use Word’s Paste Special dialog box.
Open an existing Excel file using Word’s Object dialog box.
Create a new Excel workbook using Word’s Object dialog box.
The following sections cover these methods.
The example in this section describes how to embed an Excel workbook (shown in Figure 29.6) in a Word document.
To start, select A1:C13 and copy the range to the Clipboard. Then activate (or start) Word, open the document in which you want to embed the range, and move the insertion point to the location in the document where you want the table to appear. Choose Word’s Home Clipboard Paste Paste Special command. Select the Paste option (not the Paste Link option) and choose the Microsoft Excel Worksheet Object format. Click OK, and the range appears in the Word document. Although it appears that only the range is embedded, the entire Excel workbook is actually embedded.
If you double-click the embedded object, you notice something unusual: Word’s Ribbon is replaced by the Excel Ribbon. In addition, the embedded object appears with Excel’s familiar row and column borders. In other words, you can edit this object in place by using Excel’s commands. Figure 29.7 shows the Word document after double-clicking the embedded Excel workbook. To return to Word, just click anywhere in the Word document.
Caution
Remember that no link is involved here. If you make changes to the embedded object in Word, these changes don’t appear in the original Excel worksheet. The embedded object is a copy of the original workbook, and is completely independent from the original source.
You may have noticed that Microsoft Excel Worksheet Object also appears in the Paste Special dialog box when you choose the Paste Link option. If you paste the range using this option, the workbook isn’t embedded in the Word document. When you double-click the object, Excel is activated so that you can edit the workbook.
Another way to embed an Excel workbook in a Word document is to choose Insert Text Object in Word, which displays the Object dialog box. Select the Create From File tab, click Browse, and locate the Excel document. When you click OK, a copy of the workbook is embedded in the document. No link is created.
The preceding examples embed an existing workbook into a Word document. This section demonstrates how to create a new (empty) Excel object in Word, which is useful if you’re creating a report and need to insert a table of values. If those values aren’t available in an existing Excel workbook, you can embed a new Excel object and type them.
Tip
You could insert a normal Word table, but you can take advantage of Excel’s formulas and functions in an embedded Excel worksheet.
To create a new Excel object in a Word document, choose Insert Text Object in Word. Word responds with the Object dialog box. Click the Create New tab, and you see a list of the types of objects that you can create. Select Microsoft Office Excel Worksheet from the list and click OK.
Word inserts an empty Excel worksheet object into the document and activates it for you. Again, you have full access to the Excel Ribbon, so you can enter whatever you want into the worksheet object. After you finish, click anywhere in the Word document. You can double-click this object at any time to make changes or additions.
You can change the size of the object while it’s activated by dragging any of the sizing handles (the little black squares and rectangles) that appear on the borders of the object.