Fixed Links to Excel Cells in Word

[Wannabewordwiz]

OK - I have a word document which has various links to Excel throughout it.
The excel document is a working document so sometimes new rows / columns etc
are added. I need the links I have pasted in the word document to be fixed
so they follow the movements in excel. ie - I have the number 30 in cell C1
which I have copied and pasted as a formatted text link in word. I then
insert two new rows in excel above cell c1 so now the cell the number 30
appears is is actually c3 not c1 but now my linked text in word appears as a
blank cell rather than the number 30 since it is still referring to C1 rather
than c3. I'm sure this is really simple but for some reason I just can't
figure it out!!! HELP ME PLEASE?!?!?!?

 

[macropod]

Hi,

What you need to do is to name the source cells in Excel, then modify the link fields in Word to point to those names instead of the
cell addresses. Provided the Excel objects in Word are formatted as 'in line with text', you can toggle the field code display via
alt-F9.

 

[Wannabewordwiz]

OK - thanks for this. I've named the cells in excel but now I can't figure
out how to copy them into word. When I select the text in excel, click on
copy and then paste special in word it actually pastes the cell reference and
actually when I select a range of cells which I have now named, it gives me
an error when I try to then paste special into word. Does this make sense?

 

[Peter Jamieson]

When you insert a link to an Excel range, Word inserts a LINK field - if you
use Alt-F9 to reveal Word field codes you should be able to see them -
something like

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!R1C1:R50C3 \a \f 4 \r }

for example.

If you can see that, replace the R1C1:R50C3 by the range name you created in
Excel, e.g.

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!myrangename \a \f 4 \r }

then select the field and press F9 to update the result. You can use Alt-F9
again to show the results.

 

[macropod]

Hi Peter,

When using named ranges, you should also delete the sheet reference. Thus:
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data Sources\\mytestdata.xlsx" Sheet1!R1C1:R50C3 \a \f 4 \r }
becomes:
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data Sources\\mytestdata.xlsx" myrange \a \f 4 \r }

 

[Peter Jamieson]

It depends.

In my experience, if the name references a range in the first sheet (let's
call it sheet1), then you can either insert the sheet reference or leave it
out. If the name references a range on any other sheet, you have to insert
the sheet reference.

FWIW I do not think this is how it should be (e.g. logically, I would agree
that you /should not have to/ qualify a workbook scope range name with a
sheet name if the name specifies a range on a particular sheet), or even how
it is supposed to be, but it is. Office 2007 seems to shove extra spanners
in the works and hangs rather easily with LINK fields, so it's not even that
easy to determine whether the above approach always works, let alone whether
there are other situations that can theoretically arise.

But even within Excel I don't think range names are particularly well
designed or implemented, although I am sure that Excel experts have got used
to the way they behave. I suspect part of it is down to backwards
compatibility issues in Excel (i.e. when there could only be one sheet in a
"workbook", there was no difference between a workbook scope and sheet
scope, etc. etc.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Peter,

When using named ranges, you should also delete the sheet reference. Thus:
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!R1C1:R50C3 \a \f 4 \r }
becomes:
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" myrange \a \f 4 \r }


--
Cheers
macropod
[MVP - Microsoft Word]

When you insert a link to an Excel range, Word inserts a LINK field - if
you use Alt-F9 to reveal Word field codes you should be able to see
them - something like

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!R1C1:R50C3 \a \f 4 \r }

for example.

If you can see that, replace the R1C1:R50C3 by the range name you created
in Excel, e.g.

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!myrangename \a \f 4 \r }

then select the field and press F9 to update the result. You can use
Alt-F9 again to show the results.