How to suppress Update Links warning

Question

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

User · Answer

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.

User · Answer

I don t have enough rep points to add a comment  but I want to add some clarity on the answers here   Application AskToUpdateLinks   False is probably not what you want   If set to False  then MS Excel will attempt to update the links automatically it just won t prompt the user beforehand  sorta counter-intuitive   The correct solution  if you re looking to open a file without updating links should be   Workbook Open UpdateLinks  0   Related link  Difference in AskToUpdateLinks False and UpdateLinks  0

User · Answer

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 and Sample2 xlsx and save them on C   In cell A1 of Sample1 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

User · Answer

I wanted to suppress the prompt that asks if you wish to update links to another workbook when my workbook is manually opened in Excel  as opposed to opening it programmatically via VBA   I tried including  Application AskToUpdateLinks   False as the first line in my Auto Open   macro but that didn t work  I discovered however that if you put it instead in the Workbook Open   function in the ThisWorkbook module  it works brilliantly - the dialog is suppressed but the update still occurs silently in the background     Private Sub Workbook Open         Suppress dialog  amp  update automatically without asking     Application AskToUpdateLinks   False End Sub

User · Answer

I ve found a temporary solution that will at least let me process this job   I wrote a short AutoIt script that waits for the  Update Links  window to appear  then clicks the  Don t Update  button   Code is as follows   while 1 if winexists  Microsoft Excel   This workbook contains links to other data sources    Then    controlclick  Microsoft Excel   This workbook contains links to other data sources   2  EndIf WEnd   So far this seems to be working   I d really like to find a solution that s entirely VBA  however  so that I can make this a standalone application

User · Answer

Hope to give some extra input in solving this question  or part of it    This will work for opening an Excel file from another  A line of code from Mr  Peter L   for the change  use the following   Application Workbooks Open Filename   C  Book1withLinkToBook2 xlsx   UpdateLinks  3   This is in MSDS  The effect is that it just updates everything  yes  everything  with no warning  This can also be checked if you record a macro   In MSDS  it refers this to MS EXCEL 2010 and 2013  I m thinking that MS EXCEL 2016 has this covered as well    I have MS EXCEL 2013  and have a situation pretty much the same as this topic  So I have a file  call it A  with Workbook Open event code that always get s stuck on the update links prompt   I have another file  call it B  connected to this one  and Pivot Tables force me to open the file A so that the data model can be loaded  Since I want to open the A file silently in the background  I just use the line that I wrote above  with a Windows  A xlsx   visible   false  and  apart from a bigger loading time  I open the A file from the B file with no problems or warnings  and fully updated

User · Answer

UPDATE   After all the details summarized and discussed  I spent 2 fair hours in checking the options  and this update is to dot all is   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  gt  General  gt  Ask to update automatic links checked       Trust Center  gt  Trust Center Settings     gt  External Content  gt  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  amp     xls    Do While LoopFileNameExt  lt  gt      Application DisplayAlerts   False Application Workbooks Open  InputFolder  amp  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

[excel] How to suppress Update Links warning?

Examples related to excel

Examples related to vba