Chapter 29. Sharing Data with Other Office Applications

IN THIS CHAPTER

Understanding data sharing

Pasting and linking data

Embedding objects in a worksheet

Embedding a workbook in a Word document

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.

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 subsequent changes in the source data will be reflected in the destination document.

• Embed an entire object from one application into another application's document.

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.

29.1. Copying and Pasting

Copying information from one Windows application to another is quite easy. The application that contains the information that you're copying is the source application, and the application to which you're copying the information is the destination application.

Here are the general steps that are required to copy from one application to another:

29.2. Copying from Excel to Word

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. Although I talk about Word here, most of the information in this section also applies to PowerPoint.

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 drop-down list of Word's Home Clipboard group. In addition, various paste options are available from the Paste Special dialog box, which appears when you choose Home Clipboard Paste Paste Special.

29.2.1. Pasting static information

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.

Figure 29.1. A Paste Options Smart Tag appears when you paste Excel 2010 data into a Word 2010 document.

For more control over pasting, use Home Clipboard Paste Paste Special, which displays the Paste Special dialog box (see Figure 29.2).

Figure 29.2. The Paste Special dialog box in Word.

Notice the two option buttons: Paste and Paste Link. 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 Excel Worksheet Object: You can edit this object with Excel. Double-click, and the Word Ribbon is replaced with the Excel 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.3 shows the Paste Special dialog box when an Excel chart is copied. The options are

• Microsoft Excel Chart Object: You can edit this object with Excel. Double-click, and the Word Ribbon is replaced with the Excel 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.

Figure 29.3. Word paste options when an Excel chart is on the Clipboard.

29.2.2. Pasting a link

If the Excel data that you're copying will change, you may want to paste a link. Use the Paste Link option in the Paste Special dialog box.

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.

The link is a one-way link. You cannot make changes to the linked Excel worksheet in Word.

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 onscreen, making changes to the source document, and watching for them to appear in the destination document.

You can break links rather easily. For example, if you move the source document to another directory or save it under a different name, the destination document's application can't update the link. In such a case, you need to re-establish the link manually (described later in this section).

Figure 29.4 shows the Word 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).

Figure 29.4. The Word paste link options for an Excel range.

When an Excel chart is on the Clipboard, you can also choose the Paste Link option from Word's Paste Special dialog box.

To edit (or break) a link, activate Word and choose File Info Edit Links To Files, which displays the Links dialog box shown in Figure 29.5. Select the file from the Source File list and then 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.

If the link has been broken (for example, the source file was moved or renamed), use the Change Source button to specify the source file and re-establish the link.

If the link isn't showing updated information from the source file, you can force an update by using the Update Now button.

Figure 29.5. Use Word's Links dialog box to modify or break links.

29.3. Embedding Objects in a Worksheet

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.

29.3.1. Embedding Word documents

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 then 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 of Word's features.

To embed a copy of an existing Word file, click 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 the Word Ribbon.

29.3.2. Embedding other types of documents

You can embed many other types of objects, including audio clips, video clips, MIDI sequences, and even an entire Microsoft PowerPoint presentation.

Figure 29.6 shows an MP3 audio file embedded in a worksheet. Clicking the object plays the song on the default MP3 player.

Figure 29.6. An MP3 file embedded in a worksheet.

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.

29.4. Embedding an Excel Workbook in a Word Document

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.

29.4.1. Embedding a workbook in Word by copying

The example in this section describes how to embed an Excel workbook (shown in Figure 29.7) in a Word document.

Figure 29.7. This workbook will be embedded in a Word document.

To start, select A3:C15 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 then 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 that 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.8 shows the Word document after double-clicking the embedded Excel workbook. To return to Word, just click anywhere in the Word document.

Figure 29.8. Double-clicking the embedded Excel object enables you to edit it in place. Note that Word now displays Excel's Ribbon.

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. Because the embedded object is a copy of the original workbook, it 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 select 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.

29.4.2. Embedding a saved workbook in Word

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.

29.4.3. Creating a new Excel object in Word

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.

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 then 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.