当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - 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

网友答案:

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: