Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was wondering if your superior knowledge of word could help me out with a vexing problem:
    I have a Word document with links to excel because I want the data to automatically update when I change the Excel sheet. That works fine. Then, once the info has been updated from the excel sheet to word, I want to take that info and make into unlinked text so I can email the word document to my boss without having to include the excel sheet. I know that you can copy the data that is linked to Excel, copy> paste special> paste as formatted text and just have the values of the data and not the link, but I want to do this for the entire Word document and not have to copy>paste special each time because there are well over 100 links in the Word document. I've tried copy>paste special but that doesn't break the links. I've tried to do this with Office X for OS X as well as Office 2008 for OS X. I'm going to try it on Windows tomorrow as well.
    If any one has any insight, I would be very grateful.
    Thanks,
    John

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='trapdoor' post='776931' date='26-May-2009 11:30']I was wondering if your superior knowledge of word could help me out with a vexing problem:
    I have a Word document with links to excel because I want the data to automatically update when I change the Excel sheet. That works fine. Then, once the info has been updated from the excel sheet to word, I want to take that info and make into unlinked text so I can email the word document to my boss without having to include the excel sheet. I know that you can copy the data that is linked to Excel, copy> paste special> paste as formatted text and just have the values of the data and not the link, but I want to do this for the entire Word document and not have to copy>paste special each time because there are well over 100 links in the Word document. I've tried copy>paste special but that doesn't break the links. I've tried to do this with Office X for OS X as well as Office 2008 for OS X. I'm going to try it on Windows tomorrow as well.
    If any one has any insight, I would be very grateful.
    Thanks,
    John[/quote]
    Hi John,

    Rather than unlinking the objects (which you might all too easily do on a document you'll later want to update), I'd suggest locking the links to prevent them updating. You can lock & unlock the links with code like:
    Code:
    Sub LockLinks()
    Dim oFld As Field, oShp As Shape
    For Each oFld In ActiveDocument.Fields
      If oFld.LinkFormat = True Then oFld.Locked = True
    Next
    For Each oShp In ActiveDocument.Shapes
      If oShp.Type = msoLinkedOLEObject Or _
    	oShp.Type = msoLinkedPicture Then _
    	oShp.LinkFormat.Locked = True
    Next
    End Sub
    
    Sub UnLockLinks()
    Dim oFld As Field, oShp As Shape
    For Each oFld In ActiveDocument.Fields
      If oFld.LinkFormat = True Then oFld.Locked = False
    Next
    For Each oShp In ActiveDocument.Shapes
      If oShp.Type = msoLinkedOLEObject Or _
    	oShp.Type = msoLinkedPicture Then _
    	oShp.LinkFormat.Locked = False
    Next
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When an Excel spreadsheet is linked to a Word document it is usually defined as a field. To check this out, select the excel link and press Alt+F9 - you will see the field that is pointing the spreadsheet back to the Excel file - it appears similar to { LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\Budget2009.xls" "SheetName!R9C1:R25C2" \a \p}. You can very easily strip these fields to be 'embedded' tables instead of linked.

    For your purposes, I would first copy the document (so that you can still use the sheet for future use), then select all (Ctrl+A) and strip the fields in the document to 'embedded' tables (Ctrl+Shift+F9).

    Also, you can break links through Edit, Links (towards bottom of menu) and select all the links from the dialog and choose Break Links command.

    Whichever way you find easiest. Hope this helps... trish

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='tkrokosh' post='776946' date='26-May-2009 15:06']When an Excel spreadsheet is linked to a Word document it is usually defined as a field. To check this out, select the excel link and press Alt+F9 - you will see the field that is pointing the spreadsheet back to the Excel file - it appears similar to { LINK Excel.Sheet.8 "C:\\My Documents\\Excel\\Budget2009.xls" "SheetName!R9C1:R25C2" \a \p}. You can very easily strip these fields to be 'embedded' tables instead of linked.

    For your purposes, I would first copy the document (so that you can still use the sheet for future use), then select all (Ctrl+A) and strip the fields in the document to 'embedded' tables (Ctrl+Shift+F9).

    Also, you can break links through Edit, Links (towards bottom of menu) and select all the links from the dialog and choose Break Links command.

    Whichever way you find easiest. Hope this helps... trish[/quote]
    Hi Trish,

    What you say about using Alt-F9 is true only if the Excel object is formatted as in-line with text.

    Ordinarily, if you insert a link to an Excel worksheet and format the linked object as an Excel worksheet, Word places it the document as a floating object. For such objects, Alt-F9 does not reveal the field code, and selecting the document and pressing Ctrl+Shift+F9 doesn't break the link.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •