Linking Microsoft Office Word and Excel

Assume that you want a link in your word document to a cell in your excel spreadsheet. You may know how to do it and this post is not about how to do it, but it is about a problem you may see when you link word document and excel file.
Ok for making the link, you need to copy the specified cell and paste it in your document where ever you like and the paste options button will appear and you may choose the way you’d like the link to be, these are the options you may have:
1) Keep Source Formatting: this will paste it as a text (no link) where the formatting is as it was in the excel
2) Match Destination Formatting: this will paste it as a text (no link) where the formatting is as it was in the word
3) Paste as picture: no link
4) Keep text only: still no link
5) Keep Source Formatting and Link to Excel: linked
6) Match Destination Formatting and Link to Excel: linked

* If you don’t see the "Paste Options Button" you have check your word options, in Word 2007 it is located under Office Button -> Word Options -> Advanced -> "Cut, copy and paste"

So if you want the link you have use the last two options, I mostly use the last one "Match Destination Formatting and Link to Excel".
So where is the problem? I’ll tell you, when you do that you may see the pasted text will be pasted in a new line. For example if you have something like this before setting the link:

WOexcelRD

This would look like this after the link:

WO
excel
RD

and if you try to delete the new lines and make it just like "WOexcelRD" you ma see YOU SIMPLY CAN’T.

So where is problem? the problem lies under the "Field Code – Link", if you press ALT+F9 you would see the field code link (this would toggle between Field and Field Codes) as something link this:
WO{ LINK Excel.Sheet.8 "Book1" "Sheet1!R3C4" a f 5 h * MERGEFORMAT }RD

What the heck is this? Don’t worry I’ll tell you about it in the link code mode you can insert a link code by pressing CTRL+F9 so this is the first step and will make the "GRAY { }" and now you can do the rest. The rest is something like this:
{ LINK Excel.[File name] "[Workbook name]" "[Worksheet name]!R[Row number]C[Column number]" [switches] }

And I’ll tell you what, the problem is in the SWITCHES; so what are they?
They’ll tell the excel how to link, these are some combinations I’ve found:
1. a p (Inserts a picture linked to the cell)
2. a f4 r (Inserts text with formatting using rtf)
3. a t (Inserts plain text)
4. a b (Inserts a bitmap image linked to the cell)
5. a t u (Inserts plain unicode text)

I’ve found those http://www.awomantoldme.com/women/how-to/31467957/merging-from-excel.aspx you might take a look.

But which of the will help you, I gotta tell you "You better stuck with the original one", but with a little change, I’ve found out that if you change the "h" with "r" your problem would be solved.
So here the solution:
1) Copy the cell from Excel
2) Paste it in Word and set the paste option to link
3) Press ALT+F9
4) If it was something link "a f 5 h" change it to "a f 5 r"
5) Press ALT+F9
6) Right click on the pasted text and select "Update Link"
7) This is very important: Send me a comment here and tell me about your experience 😀

But for more information about Field Codes you may refer to office help, but I’m gonna tell you what you might need.

First for inserting one in Office 2007, Goto insert->Quick Parts->Fields , Select all from Categories, and select Link from "Field names", now you can add those options one by one and see what will happen and you may also click "Field Codes" or "Hide Codes" and see the switches and what will they do, but I’m gonna tell what I’ve used:

a : Update fields automatically

f 5: Format type on update (really I don’t know what is it!!)

r : Insert object as RTF (which is the main solution)

* MERGEFORMAT: Preserve formatting during update

h : Insert object as HTML (which is the main problem)

Sadjad Bahmanpour

4 thoughts on “Linking Microsoft Office Word and Excel”

  1. You are a brilliant!  I have spent 54 hours trying to figure out why my paste link from Excel to Word would not work when I hid the Excel spreadsheet.  This posting solved it perfectly!
     
    Your easy to follow step by step instructions made it so easy for me to understand what to do:
    1) Copy the cell from Excel2) Paste it in Word and set the paste option to link3) Press ALT+F94) If it was something link "a f 5 h" change it to "a f 5 r"5) Press ALT+F96) Right click on the pasted text and select "Update Link"7) This is very important: Send me a comment here and tell me about your experience 😀
     
    Thank you very very much.  I greatly appreciate the time you took to post extremely helpful information like this.

Leave a Reply