I have a workbook that I would like to open and not have it ask to update links, (exact message is :
"This workbook contains links to other data sources. If you update the links, Excel will attempt to retrieve the latest data. If you odon't update the links, Excel will use the previous information. Note that data links can be used to access and share confidential information without your permission and possibly perform other harmful acts. Do not update the links if you do not trust the source of this workbook." )
What I would like to do is open the workbook by clicking on the file in Internet Explorer and have the links update but not ask for the user to click the button to update.
I have tried the following code in the Open Event for the work book with not success:
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
I have also tried the following lines of code in the above Sub:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.FullName, Type:=xlExcelLinks
Application.ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.FullName, Type:=xlExcelLinks
Workbooks.Open ActiveWorkbook, UpdateLinks:=True
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
The version of MS Excel 2010 and saving to an .xls file for sake of those with legacy versions.
Your help would be very appreciated. Thank you in advance for all your help.
Just in case this might help anyone in the future the following is what I did:
Private Sub Workbook_Activate() Application.AskToUpdateLinks = False End Sub
This prevented the Update Links message box from appearing when the file is opened.
Just to add to Robert's (@user2320821) answer -
I had to modify the code to:
Sub Workbook_Open() Application.DisplayAlerts = False Application.AskToUpdateLinks = False Application.DisplayAlerts = True End Sub
The key differences being that
1) It's a Workbook_Open sub instead of a Workbook_Activate sub. The Activate sub was not suppressing the Update Link request.
2) I had to throw in a DisplayAlerts flag toggle to suppress a second warning about the links not being updated, even after the first Update Link request was suppressed.
In case it wasn't obvious in Robert's answer, this sub worked when I put it in the ThisWorkbook object.