How to suppress Update Links warning?
UPDATE:
After all the details summarized and discussed, I spent 2 fair hours in checking the options, and this update is to dot all i
s.
Preparations
First of all, I performed a clean Office 2010 x86 install on Clean Win7 SP1 Ultimate x64 virtual machine powered by VMWare (this is usual routine for my everyday testing tasks, so I have many of them deployed).
Then, I changed only the following Excel options (i.e. all the other are left as is after installation):
Advanced > General > Ask to update automatic links
checked:
Trust Center > Trust Center Settings... > External Content > Enable All...
(although that one that relates to Data Connections is most likely not important for the case):
Preconditions
I prepared and placed to C:\
a workbook exactly as per @Siddharth Rout
suggestions in his updated answer (shared for your convenience): https://www.dropbox.com/s/mv88vyc27eljqaq/Book1withLinkToBook2.xlsx Linked book was then deleted so that link in the shared book is unavailable (for sure).
Manual Opening
The above shared file shows on opening (having the above listed Excel options) 2 warnings - in the order of appearance:
WARNING #1
After click on Update
I expectedly got another:
WARNING #2
So, I suppose my testing environment is now pretty much similar to OP
's) So far so good, we finally go to
VBA Opening
Now I'll try all possible options step by step to make the picture clear. I'll share only relevant lines of code for simplicity (complete sample file with code will be shared in the end).
1. Simple Application.Workbooks.Open
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx"
No surprise - this produces BOTH warnings, as for manual opening above.
2. Application.DisplayAlerts = False
Application.DisplayAlerts = False
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx"
Application.DisplayAlerts = True
This code ends up with WARNING #1, and either option clicked (Update
/ Don't Update
) produces NO further warnings, i.e. Application.DisplayAlerts = False
suppresses WARNING #2.
3. Application.AskToUpdateLinks = False
Application.AskToUpdateLinks = False
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx"
Application.AskToUpdateLinks = True
Opposite to DisplayAlerts
, this code ends up with WARNING #2 only, i.e. Application.AskToUpdateLinks = False
suppresses WARNING #1.
4. Double False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx"
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Apparently, this code ends up with suppressing BOTH WARNINGS.
5. UpdateLinks:=False
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx", UpdateLinks:=False
Finally, this 1-line solution (originally proposed by @brettdj
) works the same way as Double False: NO WARNINGS are shown!
Conclusions
Except a good testing practice and very important solved case (I may face such issues everyday while sending my workbooks to 3rd party, and now I'm prepared), 2 more things learned:
- Excel options DO matter, regardless of version - especially when we come to VBA solutions.
- Every trouble has short and elegant solution - together with not obvious and complicated one. Just one more proof for that!)
Thanks very much to everyone who contributed to the solution, and especially OP who raised the question. Hope my investigations and thoroughly described testing steps were helpful not only for me)
Sample file with the above code samples is shared (many lines are commented deliberately): https://www.dropbox.com/s/9bwu6pn8fcogby7/NoWarningsOpen.xlsm
Original answer (tested for Excel 2007 with certain options):
This code works fine for me - it loops through ALL Excel files specified using wildcards in the InputFolder
:
Sub WorkbookOpening2007()
Dim InputFolder As String
Dim LoopFileNameExt As String
InputFolder = "D:\DOCUMENTS\" 'Trailing "\" is required!
LoopFileNameExt = Dir(InputFolder & "*.xls?")
Do While LoopFileNameExt <> ""
Application.DisplayAlerts = False
Application.Workbooks.Open (InputFolder & LoopFileNameExt)
Application.DisplayAlerts = True
LoopFileNameExt = Dir
Loop
End Sub
I tried it with books with unavailable external links - no warnings.
Sample file: https://www.dropbox.com/s/9bwu6pn8fcogby7/NoWarningsOpen.xlsm
Open the VBA Editor of Excel and type this in the Immediate Window (See Screenshot)
Application.AskToUpdateLinks = False
Close Excel and then open your File. It will not prompt you again. Remember to reset it when you close the workbook else it will not work for other workbooks as well.
ScreenShot:
EDIT
So applying it to your code, your code will look like this
Function getWorkbook(bkPath As String) As Workbook
Application.AskToUpdateLinks = False
Set getWorkbook = Workbooks.Open(bkPath, False)
Application.AskToUpdateLinks = True
End Function
FOLLOWUP
Sigil, The code below works on files with broken links as well. Here is my test code.
Test Conditions
- Create 2 new files. Name them
Sample1.xlsx
andSample2.xlsx
and save them onC:\
- In cell
A1
ofSample1.xlsx
, type this formula='C:\[Sample2.xlsx]Sheet1'!$A$1
- Save and close both the files
- Delete Sample2.xlsx!!!
- Open a New workbook and it's module paste this code and run
Sample
. You will notice that you will not get a prompt.
Code
Option Explicit
Sub Sample()
getWorkbook "c:\Sample1.xlsx"
End Sub
Function getWorkbook(bkPath As String) As Workbook
Application.AskToUpdateLinks = False
Set getWorkbook = Workbooks.Open(bkPath, False)
Application.AskToUpdateLinks = True
End Function
Excel 2016 I had a similar problem when I created a workbook/file and then I changed the names but somehow the old workbook name was kept. After a lot of googling... well, didn't find any final answer there...
Go to DATA -> Edit Link -> Startup Prompt (at the bottom) Then choose the best option for you.