Problem with Covnverting Linked Excel Data to Text

Topic

New Reply

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

Reply | Quote

Replies

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

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

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
[Fmr MS MVP - Word]

Reply | Quote