2015年4月9日 星期四

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.


asktoupdatelinks


You can turn the message off in Excel Options > Advanced > Ask to update automatic links


asktoupdatelinks_02


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

沒有留言:

張貼留言