This Excel tutorial explains how to enable and disable Application AskToUpdateLinks Property (Ask to update automatic links in Excel Options).
You may also want to read
Excel Options – Ask to Update automatic links
When you link external data source outside your workbook, such as another workbook or Access database, you will be asked whether to update the data automatically when you open the workbook.
This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.
You can turn the message off in Excel Options > Advanced > Ask to update automatic links
Note that if you open the workbook which links back to a currently opened workbook, no message is prompted.
Excel VBA AskToUpdateLinks Property
When you programatically open the workbook with VBA, you need to make sure this option is off so that you can run Macro afterwards.
To make sure Ask to update automatic links Option is off, use Application Property AskToUpdateLinks as follows
Application.AskToUpdateLinks = False
To enable the option again, set the Property to True
Application.AskToUpdateLinks = True
However, user may have already turned it off by default, you don’t want to change their original setting after the Macro is run.
Since AskToUpdateLinks is a Property, which has a pair of Get and Set Methods, you can check if AskToUpdateLinks is set to True or False before you make that change, and restore the original setting when Macro is done.
Below is an example to open a workbook, disable AskToUpdateLinks and finally restore the user setting in the end.
Since AskToUpdateLinks is a global setting for all workbooks, you can check the user setting before target workbook is opened.
Public Sub openFile() userSetting = Application.AskToUpdateLinks Application.AskToUpdateLinks = False Set masterWB = Workbooks.Open("C:\Users\WYMAN\Desktop\folder\FileB.xlsx") 'Run your Macro Application.AskToUpdateLinks = userSetting End Sub