|
|
|||
Word is generally a better program than Excel for creating reports that contain a mix of narrative, tables and charts. People often use an Excel spreadsheet to calculate the values which are subsequently reproduced in a Word report. If you regularly update a report, instead of manually re-typing or copying and pasting images to transfer the data between programs, it is worth investing some time and effort to directly link the values. This will enable to your Word report to automatically update itself and incorporate any changes made to the linked Excel spreadsheet(s).
This example shows how you can link not only Excel tables and charts, but also words, dates and numbers within the paragraphs of your Word document. This saves time and also prevents careless errors where old values are overlooked and not updated.
It is best to gather all of the required data onto a single worksheet. This makes it much easier to check that the values are all updated consistently. Create tables with the formatting you wish to see in the final Word document. The purpose of this exercise is to make regular updating a simple process. It is therefore helpful to ensure that any chart titles or labels which contain values such as period descriptions are linked to worksheet cells and can be updated automatically.
You can also use Excel formulas to create words such as 'better, worse, profit, loss, January' which will feed into sentences of the report. Many of these can be simply created using IF() functions e.g. =IF(A10>B10, "decreased","increased"). Number values such as the total expenditure can also be included. Use the ABS() function to remove any negative signs which are not required if the sentence includes words such as 'profit' or 'loss'. Don't bother formatting your words and numbers.
Open a new document or edit an existing one and write the outline of your report, leaving spaces where you wish to include words, numbers, tables or charts from Excel. The next step of the project is then a complicated copy and paste exercise.
Copying words and numbers - Copy a single Excel cell containing a word or number. Position the cursor in Word and select Edit | Paste Special | Unformatted text. Then click the Paste Link option and click OK. By selecting unformatted text, the value will adopt the formatting of the words around it and will blend into your document. The Paste Link option means that it will automatically update itself to reflect any changes to the source spreadsheet.
The following extract contains 5 values pasted from Excel. The number circled in green has been formatted as currency but the value of 190 has not. The formatting style is not taken from Excel.
Word treats each of these linked values as Fields and allows you to customise the formatting of numbers. Click on a number field to select it and then press <Shift> + <F9>. The field code is then revealed. To format a number add an extra code instruction: \#"picture_code" The whole code for the field above is: {LINK Excel.Sheet.8 "D:\\MyDocs\\Excel\\xl to word example.xls" "Sheet1!R29C2" \a \t \#"£#,##0"}
The codes can be toggled and switched off by pressing <Shift> + <F9> again.
Copying a table or chart - In theory this is very simple but in reality we are faced with the unpredictable way in which pasted objects tend to jump around Word pages. It is therefore sensible to save your Word document before continuing.
Select the range of cells containing a formatted table (i.e. with borders and shading) and then Copy it. Position the cursor in Word and select Edit | Paste Special | Formatted text (RTF). Then click the Paste Link option and click OK. You may choose the Picture option instead of Formatted Text but this results in slightly larger file sizes and will result in all cell borders being printed (unless switched off in Excel).
Click once on an Excel chart and then Copy it. Position the cursor in Word and select Edit | Paste Special | Microsoft Excel chart object. Then click the Paste Link option and click OK.
When an Excel table is pasted as formatted text the rows will inherit the default Normal paragraph style. If this results in too much spacing above and below the text in each cell, try modifying the style (Format | Style | Normal | Modify | Format | Paragraph | Spacing) and set the before and after values to 0 or 1.
A table or chart can be positioned on the page (e.g. centred) using its properties. If you have problems persuading a Word object to move to the required page try unhiding the object anchors (Tools | Options | View | Object anchors). These will identify the paragraph to which a selected object is attached and the anchor can be dragged to a suitable position. It is difficult to position an object on a page unless its anchor is on the same page. If after saving and re-opening your new document, the pasted table has moved to a slightly different position, try pasting it inside the middle cell of an empty 3 column table.
If you delete a table or field which has been Paste Linked, briefly toggle on all of the field codes (<Alt> + <F9>) to ensure it was properly deleted. If necessary, delete the field code itself.
To refresh an open Word document select Edit | Links | Update Now.
To print a series of Word documents, each one with individual variables (such as different names and addresses) use mail merge with Excel as a data source.
| Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about obtaining this file |
| file: excel-to-word.htm | © meadinkent.co.uk 2006 | Last updated Sep06 |
