Excel VBA AskToUpdateLinks Property

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

Differences among Function, Sub, Method, Property

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

Outbound References

https://msdn.microsoft.com/zh-tw/library/office/ff194812%28v=office.14%29.aspx

 

Excel VBA AskToUpdateLinks Property