CODEWITHSUNDEEP
excelvba
user2320821
user2320821

Reputation: 1211

Turn Off Msg for Update Links for Excel Spreadsheet upon Opening

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
 End Sub

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
Application.ActiveWorkbook.UpdateLink
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.

Respectfully,

Robert

Upvotes: 3

Views: 44193

Answers (3)

fixform
fixform

Reputation: 1

I'm using this code in my vba .xlm file..

Private Sub Workbook_Open()
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
End Sub

M Office2013

Upvotes: 0

ma_YYC
ma_YYC

Reputation: 159

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.

Upvotes: 5

user2320821
user2320821

Reputation: 1211

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.

Robert

Upvotes: 15

Related Questions