Integrating office applications/Linking and embedding/Integrating spreadsheets

Integrating office applications

An introduction to integrating information from spreadsheets and databases into word-processing documents.
An OP Online course from the National Certificate in Computing (Level 2) programme.

About this course  |  Getting started  |  Course schedule  |  Assessment overview  |  Help

In this section we will look at how to integrate different data sources. First we need to create the files.

Activity

You are the secretary for the Otago Bunny Steeplechasers, a new club in the latest competitive sport. Membership has grown quickly. As secretary you have been asked to create a simple spreadsheet with some statistics for the club members and their rabbits. Open up Excel and enter in the following data: Save your spreadsheet as Steeplechase_results.xlsx. You can leave your sheet open to carry on with the next exercise. Embedding a spreadsheet into Word Open up a blank word document. Type in the following text: Dear members. We have the results of the first tournament. As a consequence of the TV coverage of this we have had a record number of enquiries around membership. Save this document with an appropriate name. Do not close your word document. You need to go back to the spreadsheet you completed previously. Select the range of cells for the whole table and copy. Move back to your word document. Click your mouse where you’d like your table of information to appear and choose paste. You may find that you may need to edit the width of the table to fit inside your page margins. You can also use table styles to automatically smarten it up: Save your document as Rabbits_1.docx, and close.

You can see that the process of embedding involves:

Copying a cell range from Excel ⇒ Pasting into Word (as a table.)

If we need to make changes to the embedded sheet, we can edit it directly in Word.

If we want to change the original data, we need to do this in Excel then re-copy, paste and possibly reformat the information back into Word.

Linking a spreadsheet in Word

Linking is potentially more useful than embedding. As we have said, if we have a word document that is linked to a data source, then the word document will automatically change if the data source is changed.

Activity

Open Word. In your new document type the following: Dear members. Here are the results of the first week’s trials. Congratulations to J Jones whose rabbit Bouncing for Gold did exceptionally well. Minimise your document and open your spreadsheet Steeplechase_results.xlsx. Select the table and copy. Go back to your word document. Click where you want to insert the table. From Home Tab ⇒ Clipboard, Click Paste ⇒ Paste Special The Paste Special dialogue box will appear. You need to make sure that you click on the Paste Link button and choose Microsoft Office Excel Worksheet Object from the list. Click on OK. The table will appear in your document. You can use the resizing handles to make it fit neatly within the page borders. Save your document as rabbit_2.docx and close it.

Having linked a table to Word, lets see how this works:

1. H Gustavson has discovered that her rabbit Mr Wiggle, is in fact Mrs Wiggle. Open the steeplechase spreadsheet and update this item with the new name.
2. Save and close your spreadsheet.
3. Now, re-open the document rabbit_2.docx and have a careful look at the table: you should see the changed data is now reflected in the document.
4. Save and close your documents.

Formatting a linked sheet in word

There are two ways we can access a linked spreadsheet to make changes:

• We can open Excel directly and make our changes there. We will see our changes when we open our main document.
• From our main document we can double-click on the spreadsheet object and it will open up Excel to allow us to make our edits.

Activity

Open the document rabbits_2.docx. Double click on the linked table. This will open Excel. Using what you have learned about Excel, apply some shading to the table so that it looks something like this: Manually updating linked objects. You might find that you have to “refresh” your word document in order to update changes that you have made: Right click on the object in Word - usually this would be a table. A context-sensitive menu will appear. Click on Update Link. This will force Word to manually update the table. Make sure that you use consistent formatting. For example, if you have used Arial as your letter font, make sure it is used also in your tables and charts. Make sure that colours you have used in your charts and tables will print out okay. Save your work.

|