Excel Links in Word Automatic Update when Opening Word

[bisel]

Hello All,

I have a perplexing problem hoping someone can help. This may not be an Excel problem, but it is definitely a MS Office problem.

I have Office 2016 on a Win 7 PC.

I have created a Word document where I have pasted a link to an Excel workbook chart. What I am trying to do is prevent the link from automatically updating when open the Word document. I have tried various things ... to no avail.

Here is what I have done:

• In Word 2016, I have unchecked the box for "Update automatic links on open"
• In the Word document, I have set the links in question to "Manual Update"
• In the Word document, I have enabled the "Locked" feature for the links (while in manual update mode)
• in Excel, I have unchecked the box for "Update links to other documents"

Nothing works. When I open the document, the Excel workbook source for the links, opens and an update is performed.

Anyone know what else I can do?

Thanks,

Steve

 

[Macropod]

You could try locking the LINK fields that Word uses for this. Select the linked objects and press Ctrl-F11. You can unlock them for updating if you want, via Ctrl-Shift-F11.

 

[bisel]

You could try locking the LINK fields that Word uses for this. Select the linked objects and press Ctrl-F11. You can unlock them for updating if you want, via Ctrl-Shift-F11.

Hi Paul,

Already tried that. No change.

Thanks,

Steve

 

[bisel]

Alternatively, and maybe would be best approach as this document will be sent to others ...

Does anyone have a thought about a macro that would run when the Word document opens that would disable updating links.


Regards,

Steve

 

[Macropod]

Locking them would achieve that; better still would be breaking the links. Some VBA code for the latter is:

Sub Demo()
Dim Fld As Field
For Each Fld In ActiveDocument.Fields
  With Fld
    If Not .LinkFormat Is Nothing Then .Unlink
  End With
Next
End Sub

If you'd rather just lock the fields, change '.Unlink' to '.Locked = True'.

 

[bisel]

Locking them would achieve that; better still would be breaking the links. Some VBA code for the latter is:

Sub Demo()
Dim Fld As Field
For Each Fld In ActiveDocument.Fields
  With Fld
    If Not .LinkFormat Is Nothing Then .Unlink
  End With
Next
End Sub

If you'd rather just lock the fields, change '.Unlink' to '.Locked = True'.

I need to restate the problem ...

Having set the link to manual update and locking the link prevents the object from updating ... so that works fine. But, the problem is not so much updating the link. When I open the Word document it automatically sees that there is a link(s) embedded within the document so it opens the source file (Excel workbook). It does not update the link, just performs the open, flashes a couple of times and then closes the source file without doing the update (because the link is set to manual).

How on earth can I prevent the source file from opening in the first place?

Thanks and best regards,

Steve

 

[Macropod]

Since you are sending the document to others, you don't want there to be any active links in the document, regardless of whether they're set to automatic update.

If the workbook is being opened, that strongly implies there's a link you've missed.

 

[bisel]

Since you are sending the document to others, you don't want there to be any active links in the document, regardless of whether they're set to automatic update.

If the workbook is being opened, that strongly implies there's a link you've missed.

My Excel app is one that made and sell to customers. I have many customers who are not super savvy with Office apps and have received many requests to put in a new release of my Excel app the ability to quickly produce a report. Hence my desire to include graphic objects with links to the Excel source.

I managed to create a Word macro which goes through all the links and performs and updates to a new source that the user selects at the start of the macro. It works very nicely, so that solves the problem of sending the Word document with links that would not likely function on the client's PC as their directory structure is most likely different. I think I have that situation solved.

In my testing, I have only a single object with a link to Excel source. So I have not missed a link.

Struggling to resolve this here.

Regards,

Steve

 

[Macropod]

In that case, the issue most likely has something to do with your code. Word documents with manual links to Excel do not ordinarily cause any flashing, with or without the 'automatically update links' option checked.

Conversely, Word documents with automatic links to Excel may cause a flash when the 'automatically update links' option is NOT checked but the linked workbook is open. The flashing won't occur if the linked workbook is closed.

 

[bisel]

In that case, the issue most likely has something to do with your code. Word documents with manual links to Excel do not ordinarily cause any flashing, with or without the 'automatically update links' option checked.

Conversely, Word documents with automatic links to Excel may cause a flash when the 'automatically update links' option is NOT checked but the linked workbook is open. The flashing won't occur if the linked workbook is closed.

I just created a blank Word doc and inserted a single object that linked back to the Excel source ... a chart. Changed the link to manual update. Saved the document as a docx document. There is no code in this document.

I closed Excel and closed Word. Opening the Word document and it opens the linked Excel source. As soon an the auto open macro completes in Excel, the workbook then closes.

I did the same thing and created a PowePoint single slide document. Put the same object linked to the Excel source and set the link to manual update. The PowePoint document behaves as it should ... i.e., as manual update no attempt to made to open the Excel source.

So .. there is no code in the target files and I can only conclude there is something wrong with my installation of Office 2016. So, tested this on my laptop which also has Office 2016. Same behavior.

Now, since I upgraded both my PC and the laptop from Office 2010 to Office 2016, I may have something going on here that might be related to the upgrade process.

Anyone know if there things I should be looking for in the Office 2016 services?

Thanks,

Steve