Excel Objects In Word (2003 SP-1)

Topic

New Reply

I have a problem I’m hoping someone can help with. I have an Excel 2003 single-page worksheet that contains a product database. I have also created product price books in Word 2003 where each product line item is an embedded object from the Excel spreadsheet. This works fine except when I need to edit the Excel spreadsheet to remove duplicate items, etc. Because each embedded item in Word uses an absolute cell reference in Excel, any changes (row deletions, etc., in the speadsheet wreak havoc on the Word documents.

Is there any way around this or am I stuck? Thanks in advance for any help or guidance!

Reply | Quote

Replies

Are you using links to the Excel worksheet? If so, you can assign names to cells or ranges in the worksheet, and link to those instead of to absolute cells. If you right-click the linked object in the Word document and select Toggle Field Codes, you’ll see something like

{ LINK Excel.Sheet.8 “C:ExcelBook1.xls” “Sheet1!A12” a }

If you assigned the name MyCell to Sheet1!A12, you can change the field code to

{ LINK Excel.Sheet.8 “C:ExcelBook1.xls” MyCell a }

then press F9 to hide field codes. It won’t matter now if MyCell is moved, but if you delete the cell it represents, the link won’t work any more.

Reply | Quote

Hi Hans,

Thanks very much for your help. I’m now working on a macro (that I will also be asking for help on) to name all of the ranges in my spreadsheet. There are 1300+ rows, so a macro is a neccessary! I’m posting a question about how to modify a simple macro I’ve created soon. If you have a moment, I would greatly appreciate any insights you may have on that issue as well.

Thanks again,
Bill

Reply | Quote

Another option might be to create the “table” you want word to read in the same workbook as the data but on another sheet and have the references here. Then Word can link to this “table” as this one will update itselef with changes in the data and so will not play havoc with the tables in word.

Steve

Reply | Quote

Hi Steve,

Thanks for the good input – it’s appreciated! For my purposes, I’m keeping it as simple as possible, so the first method works better for me (for now). My next post question will refer to how to make a simple macro use relative references so I can automate the range naming process for my worksheet which has around 1300 rows.

Thanks again,
Bill

Reply | Quote

Perhaps it is a little “too simple”, if it plays havoc with your word file

Steve

Reply | Quote