VBA Update Links not Updating

[InfrequentVisitor]

Hello,

I have two 2010 Excel workbooks:

Book 1 is on a shared drive and linked to multiple other Excel books within the same folder. It automatically updates when opened without a prompt to update.

Book 2 is on my local drive linked to a password protected (modify, not open) Excel book located in a different folder on my local drive. It prompts to update links, but fails to update when "Update" is selected. The cells to update contain a "#REF!" error, but nothing in the formula has a reference error. When checking the link status, it shows "OK". I have to open the linked book for the update to work.

Both workbooks use the same VBA code placed in "ThisWorkBook" to automatically update links when opened:

Private Sub Workbook_Open()

    'Update all links
    On Error Resume Next
    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

End Sub

My Trust Center options allow all Macros and requests the user to update links.

Disabling the password does nothing and co-locating the local files in the same folder only removes the prompt to update, but still fails to update. Thoughts?

Respectfully,

Eric

 

[InfrequentVisitor]

I should add that:

Book 1 links are simple cells references.

Book 2 links are SUMIFS of a Table.

- Eric