Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in Word
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?!?!?!? |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in Word
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. -- Cheers macropod [MVP - Microsoft Word] "Wannabewordwiz" wrote in message ... 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?!?!?!? |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in Word
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? |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in 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. -- Peter Jamieson http://tips.pjmsn.me.uk "Wannabewordwiz" wrote in message ... 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? |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in Word
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] "Peter Jamieson" wrote in message ... 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. -- Peter Jamieson http://tips.pjmsn.me.uk "Wannabewordwiz" wrote in message ... 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? |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Fixed Links to Excel Cells in Word
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 "macropod" wrote in message ... 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] "Peter Jamieson" wrote in message ... 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. -- Peter Jamieson http://tips.pjmsn.me.uk "Wannabewordwiz" wrote in message ... 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? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel--to-excel linked cells not updated in word mailmerge | Mailmerge | |||
Word to Excel links | Microsoft Word Help | |||
Insert Excel cells in MS-Word and retain Excel Functionalitity? | Tables | |||
Excel cells can reference other cells to display its contents/any. | Microsoft Word Help | |||
Excel Link: Adding rows in Excel does not add cells in Word | Tables |