Referencing an imbedded Excel worksheet

[Compass Rose]

I have a Word document that contains an imbedded Excel worksheet. One of the
cells of the worksheet can have an either positive or negative value. I have
some text in my document that has to be either "increased by" or "reduced
by", depending on the value of the cell in the Excel worksheet.

Can that cell be referenced with a FIELD (or otherwise)?

TIA
David

P.S. If I have to, I can change the imbedded worksheet to a table with
formulas in it, and bookmark the cell of the table, but its not my first
choice.

 

[Peter Jamieson]

In Word 2003, once you have embedded the Excel sheet, if you
a. select the cell
b. right-click, copy
c. in Word, Edit|Paste Special and choose Link and Unformatted text, then
you should see a

link field along the lines of

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My
Documents\\embedded.doc" _1224002984!Sheet1!R3C2 \a \t }

When you update the embedded object, the link also updates, but
a. I have never used this technique in a production system
b. on my test system it was very slow, and I even saw a message or dialog
box about Word wiating for an OLE operation to finish
c. I do not know what exactly the _1224002984 refers to, or in what
circumstances this reference ID might change. if you rename the document in
Windows Explorer, the link is unlikely to update correctly (you could
consider using an embedded field such as { FILENAME /p } instead but I
haven't tried).
d. you may find that the value of Word Tools|Options|General|Web
options|Files|"Update links on save" has an impact on whether or not this
field evaluates correctly

Peter Jamieson

 

[Compass Rose]

Thanks, Peter. I'm running Word 2002, but I don't think that makes a
difference.

Expanding on your thinking, I tried this:
{Ref { LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My

Documents\\embedded.doc" _1224002984!Sheet1!R3C2 \a \t }\# " 'increased by '; 'reduced by ' "}

but got this:
Error! Reference source not found.

I'm stumped. Anybody else have a brain wave?

David

 

[Peter Jamieson]

You should be able to use the numeric switch in the LINK field:

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My
Documents\\embedded.doc" _1224002984!Sheet1!R3C2 \a \t \# " 'increased by
'; 'reduced by ' "}

(A REF field has to refer to an existing /bookmark/.)

Peter Jamieson

 

[Compass Rose]

We're almost there! It works if the number is positive, but if the number is
negative, I get the actual contents of the cell [eg $(251.00)]. There must be
something wrong with the syntax, but I don't see what it is. Were you able to
get it to work at your end?

David