Sharing Data Between Applications

Office's cornerstone is data sharing among its programs. Office offers several ways to share information among products. Almost all the Office products support the inclusion of other Office files. For example, Word includes the Insert Microsoft Excel Worksheet toolbar button that inserts any Excel worksheet in a Word document.

Besides inserting entire files, you might want to insert part of a document in another Office application. The following sections explain the most common methods to embed part of one application's file inside another.

To Do: Drag and Drop

Suppose that you want to use part of an Excel worksheet inside a Word document. You might want to do this if you are preparing a report for management on last quarter's sales figures. If you have both Excel (with the relevant data loaded) and Word running at the same time, these steps quickly insert the worksheet into the Word document:

Resize your Excel and Word program windows so that you can see both the source file (the Excel worksheet) and the destination file (the Word document).
In the Excel worksheet, select the cells that you want to copy and transfer to Word.
You can use the keyboard or mouse to make the transfer. Select Edit, Copy to copy the selected cells to the Office Clipboard; click on the Word document's location where you want the cells to go; and select Edit, Paste to place the worksheet into Word. You can also hold the Ctrl key and drag one edge of the highlighted worksheet cells directly to the location in your Word document where you want to place the table and release the mouse to finalize the transfer. Figure A.1 shows the result of such a copy.

Figure A.1. This table in Word came from Excel.

If you use the mouse and do not first press Ctrl before dragging the cells, Excel will move the table from the Excel worksheet to the Word document instead of copying the table.

Depending on the size and style of your Word document, you might want to format the copied table differently from Excel's format. Right-click the table and select Format Object to display the Format Object dialog box. You can apply colors, lines, shading, and other formatting attributes to the copied object from the Format Object dialog box.

Creating Links

Suppose that you create a monthly sales report using the same Word document and the same Excel worksheet every time. Although the data within the Excel worksheet changes to reflect each new month's figures, the structure and formulas remain the same. You don't have to drag the updated Excel table to your Word document before printing the Word document each month. Instead of copying or moving the cells, you can create a link to the cells. A link points to another file.

As long as you have inserted a link to the Excel worksheet in your monthly report document's template, you need only change the Excel worksheet each month, start Word, create a report document from the template, and print the document. You won't have to copy or move the actual Excel cells into the report because the link points to the cells and displays their contents. The report always points to the worksheet cells via the link that you inserted when you created the Word document.

To Do: Link to a Worksheet

To create such a link, perform these steps:

Arrange and resize your two program windows so that you can see both the source document (the Excel worksheet) and the destination document (the Word document).
Select the cells in the Excel worksheet that you want to link and transfer to Word.
With the right mouse button, drag the edge of the highlighted cells to the location in your Word document where you want to place the table. You might have to practice doing this for a while if you are not accustomed to using the right mouse button for dragging.
Release the right mouse button. Word opens a shortcut menu with these options: Move Here, Copy Here, Link Excel Object Here, Create Shortcut Here, Create Hyperlink Here, and Cancel.
Select Link Excel Object Here to indicate to Word that you want to create an object link (as opposed to a move or a copy of the cells). Although the cells appear as though Office copied them into the Word document, the cells represent only the link that you created between the Excel source file and Word's destination document. Whenever you change the data in Excel's source file, the linked information in Word is automatically updated.

The destination file (in this case, the Word document) always reflects the most recent changes to the source file (in this case, the Excel worksheet). Therefore, if you must keep archives of old reports with the previous monthly values, you want to copy the cells instead of creating a link.

After inserting one or more links, select Edit, Links. The Links dialog box contains every link in your document and enables you to change, break, or lock any link. When you break a link, the data becomes embedded in the document and no longer updates when you update the source. When you lock a link, you temporarily prevent the link from being updated when its source is updated.

To watch the real-time nature of the links, change a value in the source Excel worksheet. The Word document immediately reflects your change. If the Word document does not reflect the change, select Edit, Links and make sure that the Automatic Update option is set. If Manual Update is set, Word will not update the link to reflect any changes in the Excel worksheet until you select Edit, Links and click the Update Now button.

Creating Shortcuts

Instead of inserting a copy or a link, you can insert a shortcut in the destination document.

You probably use shortcuts less often than links and embedded copies when producing reports because you usually want the reports to show actual data and not icons. If you often work with data from one program while in another program, however, the shortcuts are nice. The data does not get in your way until you are ready to work with it because you see only icons that represent the shortcut data. In addition, the icon loads much faster than the underlying data would load, and speed becomes an issue if you work on older computers.

When you create a shortcut from one Office program to another, Office displays a shortcut icon. If you double-click the data, Office opens the appropriate program and enables you to edit the original data using the source program that created the data. If you insert a shortcut into a non-Office program, however, Office inserts a shortcut icon that represents the data. If you double-click the icon from within the other program, the appropriate Office program begins, and you can edit the data.

Several scenarios exist where a link might work well. Suppose that you are working in Excel, modifying weekly salary figures for a large worksheet that you maintain. Each week you must study the salary amounts and enter a 10-line explanation of the salaries. Instead of typing the definition each week or ( worse ) using Excel as a limited word processor and editing the text each week, you could embed a shortcut to a Word document that contains a template for the text. When you double-click the shortcut, Excel starts Word, which automatically loads the template. You can create the final text in the template, copy the Word text into your salary worksheet (replacing the shortcut), and save the worksheet under a name that designates the current week's work.

To Do: Insert Shortcuts

To insert a shortcut, perform these steps:

Arrange and resize your program windows so that you can see both the source document (the Word template document) and the destination file (the Excel worksheet).
Select the Word template text that you want to use for the shortcut. (Press Ctrl+A if you want to select the entire Word template document.)
With the right mouse button, drag the edge of the highlighted template text to the location in your Excel worksheet where you want to place the shortcut.
Release the right mouse button.
Select Create Shortcut Here from the pop-up menu. Office creates the shortcut. As with a link, the shortcut data does not actually appear in the destination document. Unlike a link, you can drag the shortcut to your Windows desktop, to Explorer, or to another program to create additional copies of the shortcut.

Before inserting an Access table into another Office program, run a query to filter data that doesn't interest you. Hour 19, "Retrieving Your Data," explains how to develop data queries. Later in this appendix, the section titled "Using Word and Access" describes how to insert Access data into a Word document.

Inserting Hyperlinks

Thanks to the Internet, more and more users are accustomed to using hyperlinks. A hyperlink is text (or an object) that describes the link to another document or to a Web page on the Internet. When you point to the hyperlink, the mouse pointer changes to the shape of a hand. When you click the hyperlink, that linked document appears.

Perhaps you want to insert a hyperlink to a corporate Web site that an employee can click to locate information on a product's specifications. You can insert hyperlinks to other Office products, but in reality, hyperlinks are typically used for Web pages.

The nature of hyperlinks means that you will use hyperlinks most often to point to Web pages. Office 2003 makes inserting hyperlinks extremely simple if you display the actual Web page address inside your Office 2003 files. For example, if you want to embed a hyperlink inside an Excel worksheet, you only need to type the hyperlink itself and Excel automatically turns that address into a hyperlink. As soon as you type the Web address, Excel underlines the Web address and turns the address into a hyperlink to that site.

You can turn the automatic conversion of Web addresses into hyperlinks on and off. Select Tools, AutoCorrect and click the tab labeled AutoFormat as You Type. Uncheck the option labeled Internet and Network Paths with Hyperlinks when you want to type Web addresses without the Office product converting that address to a hyperlink.

Remember that you don't have to use Web addresses for hyperlinks in Office documents. You can enter path and filenames that reside on your own PC. When you or another user clicks the link, the Office product displays that link's file. Therefore, you can easily link documents together, even documents from different products within Office.

Perhaps you want something other than the Web address to appear. In other words, one of your PowerPoint presentation slides might include the text Click here to view our informative Web page , and when clicked during the presentation, the slide changes to show the actual Web page.

To Do: Convert Hyperlinks to Text

To change a hyperlink to customized text, follow these steps:

Type the Web address or path to the Office file that you want to display when the user clicks the hyperlink.
Select the hyperlink.
Press Ctrl+K to display the Insert Hyperlink dialog box shown in Figure A.2. The toolbar also displays an Insert Hyperlink button that displays the Edit Hyperlink dialog box.

Figure A.2. Use the Insert Hyperlink dialog box to change the text on the hyperlink.
Type a new value in the text box labeled Text to Display.
Click OK. Your text appears in place of the hyperlink's address.

Turning a Word Document into a Presentation

Word and PowerPoint share a special link that enables you to turn a set of notes into a presentation. As you create your notes in Word, be sure to use the Heading 1 through Heading 5 styles. PowerPoint uses the Heading 1 style for each slide's title and uses the Heading 2 through Heading 5 styles for the slide's subsequently indented text.

If your Word document contains styles other than Heading 1 through Heading 5, PowerPoint ignores those paragraphs.

You can easily apply Heading 1 through Heading 5 styles to a Word document that you have already created by clicking a paragraph (or by moving the mouse pointer anywhere within the paragraph), clicking the Style box's drop-down arrow, and selecting Heading styles from the drop-down list box. You can also select the style by opening the Style drop-down list box and scrolling to the heading style that you want to select.

After you create the Word document, open the document within PowerPoint and save the presentation. From Word itself, you can convert your document to a presentation by selecting File, Send to, Microsoft PowerPoint.

Using Word and Access

A database can be extremely large with many tables. Often, you want to export a portion of an Access database to a Word document. The drag-and-drop method does not always offer exactly what you need to get the data you want into a Word document.

If you want to use an Access table in a PowerPoint presentation, load the table into a Word document and then convert the Word document to a PowerPoint presentation as described in the previous section.

To Do: Use an Access Table in Word

To load Access data into a Word document, perform these steps:

Display the Database toolbar in Word. (Select View, Toolbars and then click Database.)
Click the Insert Database toolbar button. Word displays the Database dialog box.
Click the Get Data button to begin locating the database to import. Before specifying the data to be imported into Word, you must locate the database.
Browse to the database file you want to import. When you locate the database, select the database and Word displays the Select Table dialog box shown in Figure A.3.

Figure A.3. Indicate exactly which table Word is to use.
Select the table or query that you want to load into the Word document, and click OK to return to the Database dialog box.
Click the Query Options button if you want to limit the table's records or fields. (If you don't select the Query Options button, Word imports the entire table.) Word displays an Access query window, from which you can select records and fields using normal Access database-selection criteria. Select one or more fields that meet the criteria you specify. You can specify additional tables if you want Word to import data from multiple tables.
Click the Insert Data button to insert your selected data.
You now can format the data and use it in your Word document as if you typed the data yourself.