Referencing an imbedded Excel worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Compass Rose - 02 Nov 2006 15:53 GMT

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.

Reply to this Message

Peter Jamieson - 02 Nov 2006 21:16 GMT

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

>I have a Word document that contains an imbedded Excel worksheet. One of
>the
[quoted text clipped - 11 lines]
> formulas in it, and bookmark the cell of the table, but its not my first
> choice.

Reply to this Message

Compass Rose - 02 Nov 2006 22:20 GMT

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

> In Word 2003, once you have embedded the Excel sheet, if you
> a. select the cell
[quoted text clipped - 37 lines]
> > formulas in it, and bookmark the cell of the table, but its not my first
> > choice.

Reply to this Message

Peter Jamieson - 02 Nov 2006 23:19 GMT

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

> Thanks, Peter. I'm running Word 2002, but I don't think that makes a
> difference.
[quoted text clipped - 56 lines]
>> > first
>> > choice.

Reply to this Message

Compass Rose - 03 Nov 2006 22:05 GMT

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

> You should be able to use the numeric switch in the LINK field:
>
[quoted text clipped - 66 lines]
> >> > first
> >> > choice.

Reply to this Message

Peter Jamieson - 04 Nov 2006 10:45 GMT

It works here as long as Excel thinks the cell content is numeric. If it
thinks $(251.00) is a piece of text it won't work, at tleast partly because
Word just sees the text $(251.00) and either does not recognise that it is
negative (Word would need a minus sign and less clutter to do so, I think)
or does not recognise that it is a number at all.

So you either need to make sure your cell is formatted as a numeric or
currency value, or perhaps add a column (which you then hide) which applies
an Excel formula that ensures the result is numeric - and if you do that (I
have not tried) it might be easier to make the result of the formula the
exact text that you want in Word, and avoid having to use a \# switch.

The biggest problem I have had with this is definitely stability. Word
removes/modifies the file path name and the object reference in the LINK
field according to some "logic" that is not immediately apparent, but which
I have not been able to control.

Peter Jamieson

> We're almost there! It works if the number is positive, but if the number
> is
[quoted text clipped - 83 lines]
>> >> > first
>> >> > choice.

Reply to this Message