Thread: Automatic updating of workbook links

In Excel 2010 when I open any workbook I want to have all links to other workbooks automatically updated without prompting. According to Office 2010 Excel Help, I can achieve this for each workbook by clearing the following:

File > Options > Advanced > When calculating this workbook > Update links to other documents

Unfortunately this option does not stay cleared!

• If I go back to the option while the workbook is still open, it remains cleared.
• If I (re)open it after saving and closing, the option is checked again.

As far as I can tell, all the other options in this category keep their last settings.

Any ideas?

Create an Auto_Open Macro

    
Sub Auto_Open
ActiveWorkbook.RefreshAll
End Sub

depending on what/how your links are you may need a slightly different code. This works for me reading XML into my workbook.

Having just had another look i think this maybe what you need.

  ActiveWorkbook.UpdateRemoteReferences = True

Originally Posted by ianh

Create an Auto_Open Macro

    
Sub Auto_Open
ActiveWorkbook.RefreshAll
End Sub

depending on what/how your links are you may need a slightly different code. This works for me reading XML into my workbook.

I understand the idea of this and your later amendment to the code, but I have some questions:

1. Where can I put this code so that it will run every time I open any workbook? I do not want to have to include it in each and every workbook, but I cannot identify an Application VBA project with a suitable module.
2. Will it definitely prevent the update prompt?

I do not understand how these two settings interact with each other.

The "Ask to Update Automatic Links" setting was initially checked.

My initial experiment with clearing it seems to prevent updating for all workbooks.