By Sarah Boslaugh, Ph.D.

People working within the healthcare field often collect data in a Microsoft Office Excel 2003 workbook and then need to include some of that information in a form letter or a report created in Microsoft Office Word 2003.

For example, an environmental safety officer might record the results of quarterly inspections in a workbook and then include information from the workbook in a letter that will be sent to each lab that was inspected. Or a project manager might use a workbook to record data about the number of patients who are recruited each week for a study and then include the most recent total in a weekly report that is created in Word.

The most efficient way to accomplish this type of data sharing is to copy or link the data across the two programs so that the need to re-enter the data is eliminated. Excel and Word can share data in this way because of a standard called Object Linking and Embedding (OLE). When information is moved from Excel to Word, whether it is the value of a single cell on a worksheet or it is an entire chart or graph, it is moved as an OLE object, which is embedded in or linked to a Word document.

When to embed OLE objects

For data that is not expected to change, you should copy the selected data in an Excel workbook and paste it into a Word document to create an embedded OLE object. The embedded OLE object becomes part of the Word document and retains no connection with the Excel workbook.

When to link OLE objects

In contrast, for data that needs to be updated, you should create a link between the Excel workbook and the Word document. When you use linked OLE objects, changes in the Excel workbook will be automatically updated in the Word document.

Example of embedding and linking OLE objects

Here's a simplified example that demonstrates both embedded and linked OLE objects. You will create a form letter that has the purpose of issuing regular reports on subject recruitment for a number of research projects. For a given lab, the name of the Principal Investigator (PI) will be an embedded OLE object because it will not change, whereas the running totals of recruited subjects will be linked OLE objects because they are expected to change with each report. The letter begins as shown in the following figure.

 An embedded OLE object will be used for the PI name.

 Linked OLE objects will be used for the total number of subjects and for the number of male and female subjects.

The following figure shows the top of the corresponding Excel worksheet.

The "Smith cumulative total" row uses the SUM() function in three cells to keep running totals of the number of male, female, and male and female subjects who are recruited. These cells contain the information that you want to incorporate and update in your form letter.

Embedding OLE objects

To create a report, you need to have both the Excel workbook and the Word document open at the same time. To embed the PI's name in the first line:

1. On the Excel worksheet, copy the contents of the cell containing the PI's name.
2. In the Word document, click Paste Special on the Edit menu.
3. In the Paste Special dialog box, make sure that the Paste option button is selected, and then click Unformatted Text and click OK.

The following figure shows the result of the embedded OLE object in the form letter.

 The name of the PI on the Excel worksheet is copied and then pasted into the form letter to create an embedded OLE object.

You could just paste the contents of this cell into the Word document by clicking Edit and then clicking Paste, but using this method would retain the formatting (for example, font and text size) used in the Excel workbook, which means that you would have to change the formatting of the copied text to match the formatting used in the Word document.

Linking OLE objects

To create live links to display the cumulative number of recruited subjects:

1. On the Excel worksheet, copy the cells that contain the running totals.
2. Click in the appropriate area in the Word document, and then click Paste Special on the Edit menu.
3. In the Paste Special dialog box, make sure that the Paste link option button is selected, and then click Unformatted Text and click OK.

The following figure shows the results of the linked OLE objects in the form letter.

 Data from the "Total" column on the Excel worksheet is linked to the Word form letter to display the total number of research subjects.

 Data from the "# Male" and "# Female" columns on the Excel worksheet are linked to the Word form letter to display the specific number of male and female research subjects.

 Note   In the preceding example, the numbers 18, 10, and 8 take on the formatting of the surrounding text, as did the name "Smith," because you pasted them as unformatted text.

Updating links

Because these numbers are linked to the Excel workbook, they can be updated so that every time a new report is produced, the numbers reflect the current totals in the Excel workbook. To update a linked OLE object:

1. Open the Excel workbook and Word document.
2. In the Word document, select a linked OLE object.
3. On the Edit menu, click Update Link.

Summary

Using embedded and linked OLE objects can save you time and reduce the probability of presenting inaccurate information. You don't need to re-enter numbers in two different documents when you can copy and paste information from one document to the other. You no longer have to wonder whether you are presenting the most up-to-date information when you have set up a link between your data source and your presentation document.

About the author   Sarah Boslaugh, Ph.D., is a senior statistical data analyst in the Department of Pediatrics at the Washington University School of Medicine in St. Louis, Missouri. She has more than 20 years of experience in statistical analysis and computer programming and is an expert at using Microsoft Office PowerPoint in the medical field.