I'm trying to write a script that opens many Excel files. I keep getting the prompt:
This workbook contains links to other data sources.
I want to keep this message from appearing, so that my script can just automatically go through all the workbooks without me having to click
Don't Update for each one. Currently I'm using the following:
function getWorkbook(bkPath as string) as workbook Application.EnableEvents=False Application.DisplayAlerts=False getWorkbook=Workbooks.Open(bkPath,updatelinks:=0,readonly:=false) end function
However, the message is still appearing. How can I suppress it?
EDIT: It appears that this message is coming up for workbooks that have broken links; I wasn't seeing the
This workbook contains one or more links that cannot be updated message because I'd set
DisplayAlerts to false. The workbooks are linked to equivalent files in a folder on our Windows server, so when the matching file is deleted from that folder (which happens as part of our business flow), the link breaks. Is it possible to suppress the warning when the link is broken?
Also, I'm using Excel 2010.
After all the details summarized and discussed, I spent 2 fair hours in checking the options, and this update is to dot all
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 linkschecked:
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):
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).
The above shared file shows on opening (having the above listed Excel options) 2 warnings - in the order of appearance:
After click on
Update I expectedly got another:
So, I suppose my testing environment is now pretty much similar to
OP's) So far so good, we finally go to
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
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 (
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
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.
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!
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:
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
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