excelvba

How to suppress Update Links warning?

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.

Solution

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