Embedded Excel objects in Word

[nosremark]

I'm trying to create a Word document in Office 2003 using embedded Excel sheets as tables. The problem I'm having is not being able to insert the Excel "Sheets" as seperate objects. I need to link certain cells within each sheet to a totals table. I know how to link the objects using the "Paste Special" function but this provides a linked path to each object and will only function as long as the Word file remains in the same place. How can I link these cells between many objects and keep the link inherent to the Word document?

Thanks for any help,

ck

 

[macropod]

Hi nosremark,

If you need to link certain Word cells to cells in Excel, you'll need to link them individually, with the data pasted in RTF format. This is also what you have to do if you want the linked Excel data to become part of an existing Word table.

If you want to have a whole Excel 'table' linked to Word, you can insert it one page at a time as a series of Excel object (becasue linked objects can only span a single page), or collectively in RTF format (which allows you to span multiple pages). In either case, the linked Excel data won't become part of an existing Word table.

When linking, it doesn't matter where the Word document is stored - you can move it between drives and folders on the same machine. What matters is that the Excel workbook remains in the same location. Move to another PC, and you'll either have to edit the link paths or make the path to the workbook there the same as on the original PC.

Cheers

 

[SkipVought]

Hi,

I don't understand. I can link multiple worksheets in a Word document, save it, move it to a different folder and everyting is OK.

Please explain IN DETAIL your document configuration and the circumstances under which the link falis to update.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[nosremark]

I'm only using Excel to create the worksheets, selecting the rows/columns, copying, and using Paste Special/Office Excel Worksheet Object. When this is done the 1 embedded object has all of the worksheets from the .xls file, I want only one sheet per object. Then I need to link cells within each object (in the Word doc), the only way I know how to do this is to open the Excel object, copy the needed cell, deselect the object, open another Excel object, select the needed cell, choose paste special, "Paste Link" - Excel 8.0 Format. When I do this the cells are linked but the path looks like this:

=Excel.Sheet.8|'C:\Documents and Settings\ckramer\Desktop\Test Repair Estimate w Excel sheets.doc'!'!_1185864213!Sheet1!R8C5'

So now when I need to transfer the file to a couple of different users the path is wrong and the linked cell has no value.

Thanks again

ck

 

[macropod]

Hi nosremark,

Whichever way you embed an Excel Workbook, as opposed to linking to it, you always get the entire workbook. You can, however, decide which bits are visible.

When you link to a workbook, quite obviously, Word has to know where to find the source. That's why the path is included. Since the problem is that the links are being updated when the users you send the Word file to open it, you could have them turn of the automatic link updating option under Tools|Options|General and, perhaps, the update links option under Tools|Options|Print.

If you don't like either of those approaches, you can create an embedded Excel workbook within Word, then use Excel's std linking formulae to link only the required cells to the original workbook. In some ways, that's the best of both worlds - an embedded object that contains only as much as is needed and uses links to allow periodic updating from the source.

Failing all of the above, if you're prepared to keep the Word and Excel files in the same folder and distribute them together, I could provide a Word macro that would automatically update the links whenever the file is opened. This, of course, would depend on security settings etc allowing the macro to run ...

Cheers