# Excel Links in Word Automatic Update when Opening Word



## bisel (Apr 23, 2019)

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 (Apr 23, 2019)

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 (Apr 23, 2019)

Macropod said:


> 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 (Apr 23, 2019)

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 (Apr 23, 2019)

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 (Apr 24, 2019)

Macropod said:


> Locking them would achieve that; better still would be breaking the links. Some VBA code for the latter is:
> 
> ```
> Sub Demo()
> ...



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 (Apr 24, 2019)

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 (Apr 24, 2019)

Macropod said:


> 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 (Apr 24, 2019)

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 (Apr 25, 2019)

Macropod said:


> 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


----------



## bisel (Apr 23, 2019)

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 (Apr 25, 2019)

Try selecting the LINK field and pressing Shift-F9 to expose the field code. If you see an \a switch, delete it, then press F9 to update the display. See if that changes the behaviour on opening.


----------



## bisel (Apr 25, 2019)

∙
	
	
	
	
	
	



```

```



Macropod said:


> Try selecting the LINK field and pressing Shift-F9 to expose the field code. If you see an \a switch, delete it, then press F9 to update the display. See if that changes the behaviour on opening.



Alt-F9 is what I believe you meant to say.

The is no \a switch.

This is link field ...


```
{∙LINK∙Excel.SheetMacroEnabled.l2∙"E:\\$User\\Reserve∙Funding∙Analyzer\\Version∙Control∙and∙
Selfy\\V2.3\\Reserve∙Funding∙Analyzer∙v2.3h.xlsm"∙"Financial∙Summary∙Charts![Reserve∙Funding∙Analyzer∙
v2.3h.xlsm]Financial∙Summary∙Charts∙pctfund_chart"∙\p∙}
```

Regards,

Steve


----------



## Macropod (Apr 25, 2019)

bisel said:


> Alt-F9 is what I believe you meant to say.


No, I _meant _Shift-F9. Alt-F9 achieves much the same thing, though, except that you'd also have to press it again regardless of whether you pressed F9 after editing the field code.

If Word 2016 is doing as you say, it seems it has a bug that's not present in Word 2010, for example.


----------



## bisel (Apr 25, 2019)

Macropod said:


> If Word 2016 is doing as you say, it seems it has a bug that's not present in Word 2010, for example.



You might be absolutely correct.  I don't have a PC handy with other than Office 2016.  But, I will test that hypothesis as soon as I can.

Regards,

Steve


----------



## bisel (Apr 25, 2019)

Macropod said:


> No, I _meant _Shift-F9. Alt-F9 achieves much the same thing, though, except that you'd also have to press it again regardless of whether you pressed F9 after editing the field code.
> 
> If Word 2016 is doing as you say, it seems it has a bug that's not present in Word 2010, for example.



Hi Paul,

On the off chance that there may be something in the specific Excel workbook, I created a copy of that workbook and deleted the Private Sub Workbook_Open() macro in the "ThisWorkbook".  Once I deleted that macro, then Word performs as it should.  This does not explain why as I would thought that the macro would only get triggered when the workbook opens.  There appears to some trigger in Word - Excel interface where because of that Workbook_Open event Word is trying to open it.

Here is the code ...


```
Private Sub Workbook_Open()


On Error Resume Next


' Immediately Activate home sheet and select it
    Sheet16.Visible = True ' Home sheet
    Sheet16.Activate
    Sheet16.Select


' Application.ScreenUpdating = False


' Call unprotectsheets
Call protectsheets


' Ensure that Data Validation Sheet is unprotected
    Sheet5.Unprotect
    


' Unhide user defined columns on Sheet1
    Sheet1.HideUserHeadings_ToggleButton.Value = True
    Application.Run Sheet1.Shapes("HideUserHeadings_ToggleButton").OnAction




' Set default worksheet visibility
    Sheet1.Visible = True ' Res Comp Data
    Sheet2.Visible = xlVeryHidden ' Calc Sheet
    Sheet3.Visible = True ' Res Comp Summary
    Sheet4.Visible = True ' Comp Cost Allocation Pivot Charts
    Sheet5.Visible = xlVeryHidden  ' Data Validation
    Sheet6.Visible = True  ' Not used
    Sheet7.Visible = True ' Financial Summary charts
    Sheet8.Visible = True ' Financial Detail Tables
    Sheet9.Visible = True ' Asset Categories
    Sheet10.Visible = True ' Community summary
    Sheet11.Visible = True ' Reserve Comp Useful Life
    Sheet12.Visible = True ' Statement & Defs
    Sheet13.Visible = True ' Financial Detail Tables Gr3
    Sheet14.Visible = True ' Loans
    Sheet15.Visible = True ' Res Comp Maint Sched
    Sheet16.Visible = True ' Home sheet
    Sheet17.Visible = xlVeryHidden ' Change History
    Sheet18.Visible = True ' Income & Exp Summary
    Sheet19.Visible = True ' Financial Data Sheet


' If user defined columns on Reserve Component are blank, then hide those columns on Financial Detail sheet
    If Sheet1.Range("nonblank_usercols").Value = 0 Then
        Sheet8.Columns("B:C").EntireColumn.Hidden = True
        Range("show_user_cols").Value = False
        Range("showusercols").Value = False
    Else:
        Sheet8.Columns("B:C").EntireColumn.Hidden = False
        Range("show_user_cols").Value = True
        Range("showusercols").Value = True
        
    End If


' Set sheets auto update to True to Enable Auto Update on Financial Summary Sheets
    Sheet5.Range("auto_update").Value = "True"


' Clear the table filters in Sheet11
'    Sheet11.Select
    Application.Run ("useful_life_table_reset")
    Range("A1").Select
    
    Sheet16.Select
    


' check on workbook open if the number of years to diaplay is already set to 30
If Sheet5.Range("display_years").Value = 30 Then GoTo endmsg


    'Intialize workbook to show all 30 years ...
    
        Sheet5.Range("display_years").Value = 30
        Sheet19.ComboBox1.Value = Sheet5.Range("display_years").Value
    
   


    ' Display message that the workbook is reset to show all 30 years and
    '   user can change display years
    
 
    msg = Range("initialize_to_30").Value
            Style = vbOKOnly + vbInformation
            Title = "Reset Years on Initial Workbook Open"
            response = MsgBox(msg, Style, Title)


endmsg:


' Display first time message and ask if the user wants to show message every time.


If Range("suppress_first_time_msg") = False Then
    workbook_firstuse.Show
    Else
    End If
    
' display splash screen ... adding "false" as part of code to hide the title bar
    splash_screen.Show False
    
    Application.Run ("set_display_years")




Range("protectstatus").Select
Call protectsheets


Range("linksheets").Value = True


' Select home cell on the Financial Data Sheet
Sheet19.Range("hoaname").Select




' Close the splash screen
Unload splash_screen


' controlpanel.MultiPage1.Value = 0
controlpanel.Show


Application.ScreenUpdating = True


End Sub
```

I find that PowerPoint is better behaved with the manual link.  

Anyway, I need to research how to get around this issue.

Regards,

Steve


----------



## bisel (Apr 25, 2019)

Wow.  It seems that the mere presence of an AutoOpen macro in Excel causes a trigger to be executed which opens Excel when a link has been established in Word.

I've been testing this a couple of ways and watching the task manager when I open the Word document with manual link to the Excel workbook.  If the workbook has an AutoOpen macro then I can see that there is a trigger that opens the Excel process.  If the workbook does not have an AutoOpen macro, then it does not get triggered.

This does not occur with PowerPoint.

Steve


----------



## bisel (Apr 25, 2019)

I got this to work.

I was copying the chart in Excel and pasting to Word using Paste Special ... paste as link ... as an Excel Chart Object.  Using this option, the chart is in Word but Excel is the editor.  So when opening the Word document it triggers the Excel source to open so that one can edit the chart format, etc.  

By pasting the chart into Word as an Office Graphic Object, then the charting tools in Word can be used to edit the formatting, etc.  One can still make changes to the data in Excel and perform an "update link" in Word and those changes will be synchronized.

I tried this with a virgin Excel and Word documents and with my existing files and got same results.  

This has been a tough one to figure out.

Steve


----------

