Word 2007 generates automatic OLE links when selecting Paste Special in Excel 2007

Topic

New Reply

Using 2007 office programs. From Word 2007, copy something and then paste
into Excel 2007. THis is fine, but I want to use Paste Special and just
bring in the text. When I select paste special (not the selection of Text),
Word immediately generates an OLE link in the Word doc.

I don’t want to update the word doc with these. Can I control this at all?
Set the default to Text, so it won’t use the Word Object as a default (would
need the link).

Reply | Quote

Replies

When I select paste special (not the selection of Text), Word immediately generates an OLE link in the Word doc.

I’ve seen OLE bookmarks in Word 2003 documents, but I don’t understand why Word generates them. While someone figures out the cause, you should be able to remove those bookmarks using the Bookmarks dialog. I’m not sure where that is on the ribbon, but if you press Alt+i and then k, it should pop up. I find it fastest to select a bookmarks with the mouse (right-hand) and use Alt+d to delete it (left-hand). Your mileage may vary.

If you have an actual live link, then probably you shouldn’t delete it…

Reply | Quote

Using 2007 office programs. From Word 2007, copy something and then paste
into Excel 2007. THis is fine, but I want to use Paste Special and just
bring in the text. When I select paste special (not the selection of Text),
Word immediately generates an OLE link in the Word doc.

I don’t want to update the word doc with these. Can I control this at all?
Set the default to Text, so it won’t use the Word Object as a default (would
need the link).

Hi Ken,

The general view is that the creation of the OLE_LINK# bookmarks is caused by an Add-in, most likely Adobe Acrobat. If you close the Word document without saving changes, they’ll be deleted. If closing is inconvenient, your options are to either diable the errant Add-in before copying & pasting, or deleting the bookmarks after the event.

I see that you’ve posted the same question in microsoft.public.word.docmanagement. Please see: http://www.excelguru.ca/node/7

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the quick reply.

In my case it is a pain, as I wind up with 4-5 of these links and I am pasting the data as Text, so I do NOT want the links. It is wasting a lot of time as I have to stop and remember if I have links I have to clean out when I need to make actual data changes in the word doc.

An ordinary paste does not make the link. I think it is some odd bug (or stupid feature), since the default selection in Paste Special is Microsoft Office Word Document Object. That one would need the link, once executed. The link gets created the moment Paste Special is selected. Sadly when you select Text, it still keeps the link.

Reply | Quote

Yes, I posted on the office forum too. Mostly because I needed something quick and after giving up in finding anything for help or kb articles on the MS site. Not sure why it is harder to find stuff there. I’ll try your link, tho. Thanks

Oh, the link wasn’t any help, just a poke in the ribs about posting on 2 forums.

I had Acrobat installed, but I uninstalled it ages ago. There are no active Add-ins in either Office product.

Reply | Quote

I’ve done some more searching on OLE_LINK and find this is a common complaint starting with Word 2003. I’ve ruled out add-ins on my machine. No solutions were offered except to delete the links if you don’t want them. I just don’t want the extra work to clean up.

If those who have cleaner installs of Office 2007 can try this real quick, we can move past that one and to hopefully a way to control this or prevent it. I’d even be happy to use a macro to perform the paste as text in Excel if it wouldn’t trigger the linking.

It is simple to recreate. YOu can turn on View Bookmarks to make it easier to see when it occurs.

1. From Word, select text and then copy it
2. From Excel, select Paste Special, but take no action in the dialog
3. You will see a OLE_LINK created as seen with the [] bookmark indicators if you have View Bookmarks on.
4. If you select Text in the Excel Paste Special dialog, the link is still present.

I’ve seen in some of the search hits that when you restart in safe mode, this doesn’t occur. I suspect that is more due to what that does to not load things and not start services than disabling a Word setting.

Reply | Quote

I’ve done some more searching on OLE_LINK and find this is a common complaint starting with Word 2003. I’ve ruled out add-ins on my machine. No solutions were offered except to delete the links if you don’t want them. I just don’t want the extra work to clean up.

If those who have cleaner installs of Office 2007 can try this real quick, we can move past that one and to hopefully a way to control this or prevent it. I’d even be happy to use a macro to perform the paste as text in Excel if it wouldn’t trigger the linking.

It is simple to recreate. YOu can turn on View Bookmarks to make it easier to see when it occurs.

1. From Word, select text and then copy it
2. From Excel, select Paste Special, but take no action in the dialog
3. You will see a OLE_LINK created as seen with the [] bookmark indicators if you have View Bookmarks on.
4. If you select Text in the Excel Paste Special dialog, the link is still present.

I’ve seen in some of the search hits that when you restart in safe mode, this doesn’t occur. I suspect that is more due to what that does to not load things and not start services than disabling a Word setting.

Just tested the above steps in both Word 2007 and Word 2002 (Office XP).
In both cases, an “OLE_LINK1” bookmark is inserted.
I don’t have Acrobat installed on either machine.

The following macro will delete any OLE_LINK bookmarks in your document, after the fact:

Code:

Sub DeleteOLELINKBmks()
   Dim Bmks As Bookmarks
   Dim n As Long
   Set Bmks = ActiveDocument.Bookmarks
   For n = Bmks.Count To 1 Step -1
	  If Left$(Bmks(n).Name, 8) = "OLE_LINK" Then
		 Bmks(n).Delete
	  End If
   Next 'n
   Set Bmks = Nothing
End Sub

Gary

Reply | Quote

OK, thanks for the quick reply. I may give up on this and use your macro.

Perhaps this should go to MS and they can explain the point of all this linking and provide some way to turn this off. Linking is fine when you want to set up complex documents with dependencies, but if you want that you are already a advanced user and would “opt in” with Paste Link or embedding objects from the other Office app. I can’t imagine many users really wanting a lot of this to be done automatically for plain or formatted text. Especially without any obvious way to “opt out”

Ahh, one can dream…

Reply | Quote

OK, thanks for the quick reply. I may give up on this and use your macro.

Perhaps this should go to MS and they can explain the point of all this linking and provide some way to turn this off. Linking is fine when you want to set up complex documents with dependencies, but if you want that you are already a advanced user and would “opt in” with Paste Link or embedding objects from the other Office app. I can’t imagine many users really wanting a lot of this to be done automatically for plain or formatted text. Especially without any obvious way to “opt out”

Ahh, one can dream…

Hi Ken,

As explained in my initial post, the creation of the OLE_LINK# bookmarks is probably caused by an Add-in. That is, it’s not the default MS Word behaviour. Whilst Adobe Acrobat is one product the finger is often pointed at, I don’t believe it’s the only one. Given that a non-Microsoft product is likely to be responsible, I can’t see how Microsoft can be expected to fix it.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Ken,

As explained in my initial post, the creation of the OLE_LINK# bookmarks is probably caused by an Add-in. That is, it’s not the default MS Word behaviour. Whilst Adobe Acrobat is one product the finger is often pointed at, I don’t believe it’s the only one. Given that a non-Microsoft product is likely to be responsible, I can’t see how Microsoft can be expected to fix it.

Paul,

It’s possible this isn’t always caused by an add-in – have not previously done any research on this behavior, but did just repeat the above test on three separate PCs – with Office XP, Office 2003, and Office 2007. None of these PCs have any add-ins installed. On all of them, the OLE_LINK bookmark gets inserted. This seems to point to an inherent Word behavior.

Gary

Reply | Quote

Paul,

It’s possible this isn’t always caused by an add-in – have not previously done any research on this behavior, but did just repeat the above test on three separate PCs – with Office XP, Office 2003, and Office 2007. None of these PCs have any add-ins installed. On all of them, the OLE_LINK bookmark gets inserted. This seems to point to an inherent Word behavior.

Gary

Hi Gary,

You may be right – according to one of my contacts, it seems this behaviour is because using Paste Special opens up the possibility that the user is going to paste the object with a link to its source. If you’re pasting a link, Word needs a bookmark for the link to connect to. That’s unavoidable. Microsoft could reasonably have taken a similar view of pasting via paste Special into another OLE-aware app. However, if the user doesn’t select the ‘Paste Link’ option, I see no reason why an OLE_LINK bookmark should be created. If one is, it’s errant behaviour as far as I’m concerned. Having said that, though, I don’t see the generation of such bookmarks when they’re not needed as being a significant issue.

Regarding your macro, there’s a potential problem in simply killing off all OLE_LINK bookmarks – some may be in use, either within the document in which they’re found, or by another OLE-aware application (eg Excel). Unfortunately, except for links within the same document, there’s no way of retrieving any information from that document as to which OLE_LINK bookmarks might be in use. For possible intra-document usage, you could test for the existence of any LINK fields pointing to the OLE_LINK bookmarks.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Gary,

You may be right – according to one of my contacts, it seems this behaviour is because using Paste Special opens up the possibility that the user is going to paste the object with a link to its source. If you’re pasting a link, Word needs a bookmark for the link to connect to. That’s unavoidable. Microsoft could reasonably have taken a similar view of pasting via paste Special into another OLE-aware app. However, if the user doesn’t select the ‘Paste Link’ option, I see no reason why an OLE_LINK bookmark should be created. If one is, it’s errant behaviour as far as I’m concerned. Having said that, though, I don’t see the generation of such bookmarks when they’re not needed as being a significant issue.

Regarding your macro, there’s a potential problem in simply killing off all OLE_LINK bookmarks – some may be in use, either within the document in which they’re found, or by another OLE-aware application (eg Excel). Unfortunately, except for links within the same document, there’s no way of retrieving any information from that document as to which OLE_LINK bookmarks might be in use. For possible intra-document usage, you could test for the existence of any LINK fields pointing to the OLE_LINK bookmarks.

Hi Paul,

Good points. Even if it is an unnecessary behavior, it doesn’t seem like much of problem, in terms of it having any functional impact. I know I’ve been seeing these links in documents for years; always just shrugged it off as a quirk of Word behavior.

You’re right that the macro posted should be used with caution.

Gary

Reply | Quote