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]

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]

∙

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]

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]

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]

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]

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]

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