locked
Linking larger Excel worksheets into MS Word RRS feed

  • Question

  • In my Word document I wish to link an Excel .xls worksheet. It links. It shows in Word. But only one page. Is there something I don't get about this? Seems reasonable to be able to do this. I can cut and paste, but I'm trying to automate a process that produces a report.  Thanks.
    Wednesday, January 9, 2013 9:14 PM

Answers

  • Hi,

    If you want to insert the whole workbook, then we need to insert the Excel file object.

    1. In Word, Insert tab -> click Object button in Text group
    2. Then Object dialog box will show. Select Create from File tab.
    3. Browse to find the file, check Display as icon and Link to file check box.

    Then it will insert the Excel workbook into Word as an icon. Double click the icon, it will open the Excel workbook.

    If you have any feedback on our support, please click here


    Jaynet Zhang
    TechNet Community Support


    • Edited by Jaynet Zhang Thursday, January 10, 2013 5:50 AM
    • Marked as answer by Jaynet Zhang Monday, January 14, 2013 2:51 AM
    Thursday, January 10, 2013 5:50 AM
  • If you insert the link using a graphical format, you only see one page, so you have to choose a text format, I.e. do Edit->Copy in Excel, then Edit->Paste Special in Word, select the paste link button, then choose a format such as Rich Text (RTF). On word 2007 and later, you get to Paste Special via the down-arrow under the paste button.

    Peter Jamieson

    • Marked as answer by Jaynet Zhang Monday, January 14, 2013 2:51 AM
    Thursday, January 10, 2013 7:56 AM

All replies

  • Hi,

    If you want to insert the whole workbook, then we need to insert the Excel file object.

    1. In Word, Insert tab -> click Object button in Text group
    2. Then Object dialog box will show. Select Create from File tab.
    3. Browse to find the file, check Display as icon and Link to file check box.

    Then it will insert the Excel workbook into Word as an icon. Double click the icon, it will open the Excel workbook.

    If you have any feedback on our support, please click here


    Jaynet Zhang
    TechNet Community Support


    • Edited by Jaynet Zhang Thursday, January 10, 2013 5:50 AM
    • Marked as answer by Jaynet Zhang Monday, January 14, 2013 2:51 AM
    Thursday, January 10, 2013 5:50 AM
  • If you insert the link using a graphical format, you only see one page, so you have to choose a text format, I.e. do Edit->Copy in Excel, then Edit->Paste Special in Word, select the paste link button, then choose a format such as Rich Text (RTF). On word 2007 and later, you get to Paste Special via the down-arrow under the paste button.

    Peter Jamieson

    • Marked as answer by Jaynet Zhang Monday, January 14, 2013 2:51 AM
    Thursday, January 10, 2013 7:56 AM
  • Thank you. But this doesn't automate the process. I want all this to happen automatically when I open the Word document it's there. It should automate the cut-and-paste process.
    Monday, January 14, 2013 11:34 AM
  • Thank you. I know how to do cut-and-past. But that doesn't automate the process. This should be simple.
    Monday, January 14, 2013 11:35 AM
  • What do you need to automate, exactly? Unfortunately, in the general case, it isn't simple.

    Do you need to open a new Word document then insert the contents of a named Excel sheet in a named workbook? Or is it the first sheet? Does the sheet have to be linked or can it just be pasted? Does it have to be the entire sheet (e.g., down to the rightmost, bottommost non-empty cell) or will you specify the range? What if it's very wide?

    Reasons for asking some of this stuff include

     a. if you don't specify the sheet name, you'll get the currently active sheet, or the sheet active when last saved (and if the sheet is open, you will get a different result than if the sheet is closed)

     b. if you know the sheet name and range, you may be able to insert a link without even opening Excel directly to automate it - you can insert a field like

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" "Sheetname!RaCb:RyCz" \a \f 4 \r }

    or

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" "Sheetname!rangename" \a \f 4 \r }

    if you have a range name. In some cases,

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" "rangename" \a \f 4 \r }

    may work.

    Then you execute the field.

    Otherwise you have to Automate Excel to find out 

     - the name of the first sheet, if that's what you need

     - the range that the sheet actually occupies. This is because you can't supply the sheet name alone, e.g.

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" "sheetname" \a \f 4 \r }

    or

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" "sheetname$" \a \f 4 \r }


    Peter Jamieson

    Monday, January 14, 2013 2:21 PM
  • Thank you for your reply.

    I could always go to the sheet and cut and paste it into the word document, but I'm trying to achieve automatic publishing here. So .. I go to word and insert object. Check "link".

    When I open the word document, it does exactly what I want .. it brings in the content from the workbook (there's only one sheet).

    BUT -- IT ONLY BRINGS IN ONE PAGE WORTH. It clips it to one page. I want it NOT to clip it at one page. It treats it as though it's a picture and just CLIPs it! That's the problem. As far as I can tell, all the other variables are irrelevant.

    Thanks.

    j

    Monday, January 14, 2013 4:37 PM
  • I know it only brings in one page's worth. That's why I said you had to do it a different way. It's because the sheet is pasted as a graphic object and they can't span pages. The other two ways you can do it are

     a. the way I mentioned

     b. using  DATABASE field.

    To automate (a) you could use this bit of VBA:

    Sub makeRTFLinktoXL(TargetRange As Word.Range, _
      XLWBFullName As String)
    ' Insert the content of an Excel workbook
    ' with a single sheet
    Dim f As Word.Field
    Set f = TargetRange.Fields.Add(Range:=TargetRange, _
      Type:=Word.WdFieldType.wdFieldEmpty, _
      Text:=" LINK Excel.Sheet.8 """ & _
      Replace(XLWBFullName, "\", "\\") & _
      """ \a \f 4 \r ", _
      preserveformatting:=False)
    ' if you want to leave the link in the document,
    ' comment out the following line
    f.Unlink
    End Sub

    ' for example

    Sub testMakeRTFLinktoXL()
    ' Assume the sheet is in c:\a\test.xls
    ' and you are inserting into an empty document
    Call makeRTFLinktoXL(ActiveDocument.Content, _
      "c:\a\test.xls")
    End Sub


    Peter Jamieson

    Monday, January 14, 2013 5:25 PM
  • Thank you. I hate to be obtuse, but is there a way automatically to do this when opening the word document .. and, oh, BTW, where does it put this object?

    Also, ff a subroutine call has to be made, then it isn't automatic, unless the subroutine call can be made automatically (on open). Excel has a data function to update a cell/range with the contents of an external file ON OPEN. This is what I'm looking for in Word.

    Thank you for your patience.

    j

    Monday, January 14, 2013 8:46 PM
  • I'm hoping (1) will cover it - otherwise, I've tried to outline some options, but they all require you to dive somewhat deeper into Word than you had probably hoped.  

    1. If the name of the workbook that you want to include is fixed, then you can insert the LINK field in your Word document manually, where you want it, manually. If you save and close the document, modify your Excel workbook, then re-open the document, the new data should appear (it can take a short while for that to happen). So in that scenario, you're done, except...

    2. ...that behaviour does depend on a setting in the Word Application (i.e., not a per-document setting). (In VBA you can set Application.Options.UpdateLinksAtOpen = True ), and may be affected by other things (for example, there is a registry setting that affects the behaviour of links). There is also an "UpdateLinksAtPrint" option, BTW.

    So you either need to know that the machine you are using for this task has Word set up the way you want, or further action will be needed. In my view, that "further action" would consist of updating the link programmatically rather than trying to set the relevant options in Word. For example, the following code could be used to do that:

    Sub updateLinks(theDocument As Word.Document)

    theDocument.Fields.Update

    End Sub

    ' you can test it...

    Sub testUpdateLinks()

    call updateLinks(ActiveDocument)

    End Sub

    Where to put that code depends partly on how you are executing your process. If you are hoping to be able merely to print the Document, and have Word automatically update all its links before you do so, then I think you would have to make the document a .docm (document with macros) or old-style .doc,  and put the code in an AutoOpen macro, like this:

    Sub AutoOpen()

    call updateLinks(ActiveDocument)

    ' or you could just do this:

    ' ActiveDocument.Fields.Update

    End Sub

    Sub updateLinks(theDocument As Word.Document)

    theDocument.Fields.Update

    End Sub

    (FWIW I don't know for sure that that will work as I would never do things that way myself). There's more information on how to get Word to run macros here:

    http://word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm

    If you are controlling your process using something such as Excel VBA or VBScript, then you can either put the macro code in the Word .doc or .docm, or you can put it in a template  that your Word document is atached to (a .dot or .dotm, but don't put it in the Normal template), or you can put it in your controlling macro. Typically, such a controller would do something like this:
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("the pathname of the document")

    ' if you want your controlling macro to do the work, it needs to do this...

    call updateLinks(theDoc)

    ... and so on, and you would put the updateLinks Sub somewhere in the controller's code.

    3. If the workbook name is not fixed, then you will in any case need to use code along the lines that I suggested in my previous message. In that case, the code would probably need to be in the controller, because that would presumably "know" the name of the workbook to insert, and the Word document itself would not know. The other thing it needs to know is where to put the LINK field, or how to find the LINK field and modify the file name in it. There are various possible approaches to that. Probably the simplest is to omit the LINK field and manually insert a bookmark (Insert->Bookmark) in the document at the point where you want your Excel sheet. Then your code could be something like this:

    .

    .

    Dim strXLWBFullName As String

    ' at some point you set the name of the workbook

    strXLWBFullName = "the full path name of the workbook"

    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("the pathname of the document")

    ' let's assume the bookmark is named "XLSheet1"

    call makeRTFLinkToXL(objDoc.Bookmarks("XLSheet1"), strXLWBFullName)

    ' and so on. IN the same module or elsewhere, you would have this:

    Sub makeRTFLinktoXL(TargetRange As Word.Range, _
      XLWBFullName As String)
    ' Insert the content of an Excel workbook
    ' with a single sheet
    Dim f As Word.Field
    Set f = TargetRange.Fields.Add(Range:=TargetRange, _
      Type:=Word.WdFieldType.wdFieldEmpty, _
      Text:=" LINK Excel.Sheet.8 """ & _
      Replace(XLWBFullName, "\", "\\") & _
      """ \a \f 4 \r ", _
      preserveformatting:=False)
    ' if you want to leave the link in the document,
    ' comment out the following line
    f.Unlink
    End Sub

    ' for example

    Sub testMakeRTFLinktoXL()
    ' Assume the sheet is in c:\a\test.xls
    ' and you are inserting into an empty document
    Call makeRTFLinktoXL(ActiveDocument.Content, _
      "c:\a\test.xls")
    End Sub



    Peter Jamieson

    Tuesday, January 15, 2013 11:02 AM
  • If you are always including the same workbook, then all you should really need is to create the correct LINK field, manually, in the Word document. When it opens, it should update the LINK automatically. The link would be like this

    { LINK Excel.Sheet.8 "c:\\folder\\wbname.xls" \a \f 4 \r }

    As long as you control the environment in which your process is running, the main thing (I think) that would prevent that from happening is that Word has an option (i.e. it's not a per-document option) to enable/disable automatic links (a) At Open and (b) At Print time. Those need to be set correctly. (You can do it in Word VBA using e.g. Application.Options.UpdateLinksAtOpen = True an so on.

    If you don't control the environment, more will be needed. It's not complicated, but I'd rather not go there unless it's necessary.

    If the workbook is not fixed, i.e. is chosen somehow during your process, then your process would need to know how to find the LINK field, or where to put it. Because the document itself would not know what filename to insert, the code to insert/modify the field would almost certainly need to be in your process controller's code, and we don't know what that is. So again I'd rather not go there in detail unless it's necessary, but I would do it this way:

     a. in the Word document, insert a bookmark instead of the LINK field. Call it "XLSheet1", say

     b. In your controller code, at some point you will need to get a reference to the Word object, then open the document. At its simplest, the code in VBA would look like this:

    Dim objWord as Word.Application

    Dim objDoc as Word.Document

    Dim strXLWBFullName As String

    ' somewhere you would define the name of the workbook...

    strXLWBFullName = "c:\a\test.xls"

    Set objWord = CreateObject("Word.Application")

    Set objDoc = objWord.Documents.Open("the full path name of your document")

    Call makeRTFLinktoXL(objDoc.Bookmarks("XLSheet1"), strXLWBFullName)

    ' etc.


    Peter Jamieson

    Tuesday, January 15, 2013 11:49 AM
  • Thank you.

    It turns out that this can/should all be done very simply. My Excel stuff saves as *.prn. I link to it as a text link. It inserts properly. HOWEVER, it doesn't update automatically. I make sure the appropriate checkbox is checked for "automatically update links when opening file". It's checked. I try again. For sure, it doesn't update. So, I hover over the field, right click, select "update". It updates.

    There's a thread somewhere else: http://social.technet.microsoft.com/Forums/en-US/word/thread/6760d1e2-2f10-43b1-881e-4e4f77e8b402/ that discusses the problem of not updating. The thread ends with something like "we're looking into this".

    That's the end of the thread.

    I'll try Word 2013 now and see what happens, but that won't solve my problem because this must run with Word 2010. I'll also try some other machine.

    Thank you for your comments. Word VBA will be on my list when I have time to fool around.

    j

    Wednesday, January 16, 2013 7:51 PM
  • Which type of printer are you using to produce the .prn? Is it the plain text printer?

    How are you making the link to it? Are you using Insert->Object->Text from File, then specifying that you want to Insert as a Link? 

    If so, what Word is probably inserting is an INCLUDETEXT field, which is always considered to be a manual link as far as I know. If what you want to do is print the document, then the setting most likely to make this field update is 

    application.Options.UpdateFieldsAtPrint = True

    It's also possible that this is affected by a registry setting.

    If necessary, you can always set up these link/field update settings in Word using an AutoExec macro in Word's Normal.dotm template. It's not ideal, but it may help.

    If you aren't doing things that way, if you still need assistance you would need to spell how you are doing things.


    Peter Jamieson

    Thursday, January 17, 2013 9:55 AM
  • I've done this with a simple text file -- test.txt -- created with notepad. Printer is not a variable, though originally I tried to solve this problem by saving as *.prn.

    Doing insert->object->text from file .. as link. Checkbox for update fields on open is checked.

    Seems to me this should work, but it doesn't.

    I'll look at the normal.dotm template.

    Seems to me that this feature is broken. I would like to see just ONE case where it works, then I could go from there. VBA should not be necessary.

    John Elkins

    Thursday, January 17, 2013 2:46 PM
  • Seems to me this should work, but it doesn't.

    Well, it depends on what you mean by "should". We all might hope that it would, and find it easier to do things without VBA, but about 10 years ago, Microsoft had a blitz on security to prevent people from using Word documents as a mechanism for "phishing" material from other places such as disk files etc. So fields such as LINK, INCLUDETEXT were changed so that they did not automatically "fire" when the document opened. At the time, it was possible to put a value in the Windows registry (it's called FieldCalcSecurityLevel and is documented here: http://support.microsoft.com/kb/330079. So the fact that INCLUDETEXT fields are not updated at open is how Word is now "designed" to work.

    Using Insert->Object->Text from file and selecting the LINK object gives you an INCLUDETEXT field. Since there isn't a Checkbox for update fields on open in that dialog, I think you must be referring to the one in Word options, but that's a checkbox to update links on open, not fields. And this is where the thing about the different types of link being treated differently comes in. You can certainly set the value to update fields on print, but I'm assuming now that that is not what you need.

    The trouble is that I don't think that the FieldCalcSecurityLevel value can be used to override the behaviour of Word (as far as INCLUDETEXT fields are concerned) in Word 2010. I just tried here, and it doesn't appear to have any effect. But by all means verify that for yourself. Personally I think it should be possible to revert to the old behaviour, especially since Microsoft introduced a whole new mechanism for users to tell Word that files were to be regarded as safe (i.e. marking folders as trusted). But that mechanism doesn't seem to come into play in these cases.

    So that is why I suggested that you went for the LINK field approach - it's because you can set Word so that it will update those LINK fields at open. But even there I have to say that I am not convinced that it works in all circumstances.



    Peter Jamieson

    Thursday, January 17, 2013 5:35 PM
  • Peter: I did a search on that registry key and saw that lots of other people had the same question about how to automate documents and automatic updating of links not working. It seems that the feature simply does not exist. Microsoft should clear things up by removing any reference to "automatic updates" from Word. -j
    Friday, January 18, 2013 12:57 PM