Advertisement
Advertisement



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.

2019/07/11
1
110
7/11/2019 3:50:05 PM

Accepted 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.

2013/02/16
2
2/16/2013 6:43:46 PM


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:

enter image description here

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

  1. Create 2 new files. Name them Sample1.xlsx and Sample2.xlsx and save them on C:\
  2. In cell A1 of Sample1.xlsx, type this formula ='C:\[Sample2.xlsx]Sheet1'!$A$1
  3. Save and close both the files
  4. Delete Sample2.xlsx!!!
  5. 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
2013/02/17

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.

2018/08/12

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 & update automatically without asking
    Application.AskToUpdateLinks = False
End Sub
2013/05/17

(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

2018/12/11

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.

2015/11/19