vba 如何抑制更新链接警告？

How to suppress Update Links warning?

提问by sigil

I'm trying to write a script that opens many Excel files. I keep getting the prompt:

我正在尝试编写一个打开许多 Excel 文件的脚本。我不断收到提示：

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 Updatefor each one. Currently I'm using the following:

我希望不出现此消息，以便我的脚本可以自动浏览所有工作簿，而不必单击Don't Update每个工作簿。目前我正在使用以下内容：

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 updatedmessage because I'd set DisplayAlertsto 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?

编辑：对于链接断开的工作簿，似乎会出现此消息；我没有看到该This workbook contains one or more links that cannot be updated消息，因为我设置DisplayAlerts为 false。工作簿链接到我们 Windows 服务器上一个文件夹中的等效文件，因此当从该文件夹中删除匹配的文件时（这是我们业务流程的一部分），链接将中断。链接断开时是否可以抑制警告？

Also, I'm using Excel 2010.

另外，我使用的是 Excel 2010。

采纳答案by sigil

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:

我找到了一个临时解决方案，至少可以让我处理这项工作。我编写了一个简短的 AutoIt 脚本，等待“更新链接”窗口出现，然后单击“不更新”按钮。代码如下：

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.

到目前为止，这似乎正在奏效。然而，我真的很想找到一个完全是 VBA 的解决方案，这样我就可以使它成为一个独立的应用程序。

回答by Peter L.

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.

在总结和讨论了所有细节之后，我花了 2 个小时来检查选项，而这次更新是为了打点所有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).

首先，我在由 VMWare 提供支持的 Clean Win7 SP1 Ultimate x64 虚拟机上执行了干净的 Office 2010 x86 安装（这是我日常测试任务的常规例行程序，因此我部署了许多）。

Then, I changed only the following Excel options (i.e. all the other are left as is after installation):

然后，我只更改了以下 Excel 选项（即安装后所有其他选项都保持原样）：

• Advanced > General > Ask to update automatic linkschecked:

• Advanced > General > Ask to update automatic links检查：

• 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):

• Trust Center > Trust Center Settings... > External Content > Enable All...（尽管与数据连接相关的那个很可能对这种情况并不重要）：

Preconditions

先决条件

I prepared and placed to C:\a workbook exactly as per @Siddharth Routsuggestions in his updated answer (shared for your convenience): https://www.dropbox.com/s/mv88vyc27eljqaq/Book1withLinkToBook2.xlsxLinked book was then deletedso that link in the shared book is unavailable (for sure).

我C:\完全按照@Siddharth Rout他更新后的答案中的建议准备并放置到工作簿中（为了您的方便而共享）：https://www.dropbox.com/s/mv88vyc27eljqaq/Book1withLinkToBook2.xlsx 然后删除了链接的书，以便共享中的链接书不可用（当然）。

Manual Opening

手动开启

The above shared file shows on opening (having the above listed Excel options) 2 warnings - in the order of appearance:

以上共享文件在打开时显示（具有上面列出的 Excel 选项）2 个警告 - 按出现顺序：

WARNING #1

警告 #1

After click on UpdateI expectedly got another:

单击后，Update我预计会得到另一个：

WARNING #2

警告#2

So, I suppose my testing environment is now pretty much similar to OP's) So far so good, we finally go to

所以，我想我的测试环境现在非常类似于OP's) 到目前为止一切顺利，我们终于去

VBA Opening

VBA 开口

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

1.简单的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

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 = Falsesuppresses WARNING #2.

此代码以WARNING #1结束，单击任一选项 ( Update/ Don't Update)不会产生进一步的警告，即Application.DisplayAlerts = False抑制WARNING #2。

3. Application.AskToUpdateLinks = False

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 #2only, i.e. Application.AskToUpdateLinks = Falsesuppresses WARNING #1.

与 相反DisplayAlerts，此代码仅以WARNING #2结束，即Application.AskToUpdateLinks = False抑制WARNING #1。

4. Double False

4.双重错误

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.

显然，这段代码最终抑制了BOTH WARNINGS。

5. UpdateLinks:=False

5.更新链接：=假

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 WARNINGSare shown!

最后，这个 1-line 解决方案（最初由 提出@brettdj）的工作方式与 Double False 相同：不显示警告！

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:

除了良好的测试实践和非常重要的解决案例（我可能每天在将我的工作簿发送给 3rd 方时都会遇到这样的问题，现在我已经准备好了），另外还学到了两件事：

1. Excel options DO matter, regardless of version - especially when we come to VBA solutions.
2. Every trouble has short and elegant solution - together with not obvious and complicated one. Just one more proof for that!)

1. 无论版本如何，Excel 选项都很重要 - 特别是当我们使用 VBA 解决方案时。
2. 每一个麻烦都有简短而优雅的解决方案 - 以及不明显和复杂的解决方案。再证明一个！）

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)

非常感谢为解决方案做出贡献的每个人，尤其是提出问题的 OP。希望我的调查和彻底描述的测试步骤不仅对我有帮助）

Sample file with the above code samples is shared (many lines are commented deliberately): https://www.dropbox.com/s/9bwu6pn8fcogby7/NoWarningsOpen.xlsm

上面代码示例的示例文件共享（多行故意注释）：https: //www.dropbox.com/s/9bwu6pn8fcogby7/NoWarningsOpen.xlsm

Original answer(tested for Excel 2007with certain options):

原始答案（使用某些选项针对Excel 2007进行测试）：

This code works fine for me - it loops through ALL Excel files specified using wildcards in the InputFolder:

这段代码对我来说很好用 - 它循环遍历使用通配符指定的所有 Excel 文件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

示例文件：https: //www.dropbox.com/s/9bwu6pn8fcogby7/NoWarningsOpen.xlsm

回答by Siddharth Rout

Open the VBA Editor of Excel and type this in the Immediate Window (See Screenshot)

打开 Excel 的 VBA 编辑器并在立即窗口中输入（见截图）

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.

关闭 Excel，然后打开您的文件。它不会再次提示您。请记住在关闭工作簿时重置它，否则它也不适用于其他工作簿。

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.

Sigil，下面的代码也适用于链接断开的文件。这是我的测试代码。

Test Conditions

测试条件

1. Create 2 new files. Name them Sample1.xlsxand Sample2.xlsxand save them on C:\
2. In cell A1of 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.

1. 创建 2 个新文件。它们命名Sample1.xlsx，并Sample2.xlsx和它们保存在C:\
2. 在 的单元格A1中Sample1.xlsx，键入此公式='C:\[Sample2.xlsx]Sheet1'!$A$1
3. 保存并关闭这两个文件
4. 删除Sample2.xlsx！！！
5. 打开一个新工作簿，它的模块粘贴此代码并运行Sample. 您会注意到您不会收到提示。

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

回答by Renwick Wright

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 = Falseas 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 ThisWorkbookmodule, it works brilliantly - the dialog is suppressed but the update still occurs silently in the background.

当我在 Excel 中手动打开我的工作簿（而不是通过 VBA 以编程方式打开它）时，我想取消询问您是否希望更新指向另一个工作簿的链接的提示。我尝试包括：Application.AskToUpdateLinks = False作为我Auto_Open()宏中的第一行，但这没有用。然而，我发现如果你把它放在模块中的Workbook_Open()函数中ThisWorkbook，它会工作得很好 - 对话框被抑制，但更新仍然在后台静默发生。

 Private Sub Workbook_Open()
    ' Suppress dialog & update automatically without asking
    Application.AskToUpdateLinks = False
End Sub

回答by user2060451

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

Excel 2016 我在创建工作簿/文件然后更改名称时遇到了类似的问题，但以某种方式保留了旧的工作簿名称。经过大量的谷歌搜索......好吧，没有在那里找到任何最终答案......

Go to DATA -> Edit Link -> Startup Prompt (at the bottom) Then choose the best option for you.

转到 DATA -> Edit Link -> Startup Prompt（在底部）然后选择最适合您的选项。

回答by Donaldo Almazan

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

Application.AskToUpdateLinks = False 可能不是您想要的。

If set to False, then MS Excel willattempt to update the links automatically it just won't prompt the user beforehand, sorta counter-intuitive.

如果设置为 False，那么 MS Excel将尝试自动更新链接，它不会事先提示用户，有点违反直觉。

The correct solution, if you're looking to open a file withoutupdating links should be:

如果您希望在不更新链接的情况下打开文件，则正确的解决方案应该是：

Workbook.Open(UpdateLinks:=0)

Workbook.Open（更新链接：=0）

Related link: Difference in AskToUpdateLinks=False and UpdateLinks:=0

相关链接： AskToUpdateLinks=False 和 UpdateLinks:=0 的区别

回答by JDF

Hope to give some extra input in solving this question (or part of it).

希望在解决这个问题（或部分问题）方面提供一些额外的投入。

This will work for opening an Excelfile from another. A line of code from Mr. Peter L., for the change, use the following:

这将适用于Excel从另一个打开文件。Peter L. 先生的一行代码，对于更改，请使用以下内容：

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.

这是在MSDS. 结果是它只是在没有警告的情况下更新所有内容（是的，所有内容）。如果您录制宏，也可以检查这一点。

In MSDS, it refers this to MS EXCEL 2010and 2013. I'm thinking that MS EXCEL 2016has this covered as well.

在 中MSDS，它指的是MS EXCEL 2010和2013。我认为这MS EXCEL 2016也涵盖了这一点。

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_Openevent code that always get's stuck on the update links prompt. I have another file (call it B) connected to this one, and Pivot Tablesforce me to open the file Aso that the data model can be loaded. Since I want to open the Afile 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 Afile from the Bfile with no problems or warnings, and fully updated.

我有MS EXCEL 2013，并且情况与此主题几乎相同。所以我有一个A带有Workbook_Open事件代码的文件（调用它），它总是卡在更新链接提示上。我有另一个文件（称为B）连接到这个文件，数据透视表强制我打开文件A以便可以加载数据模型。由于我想A在后台静默打开文件，所以我只使用我上面写的那行，带有Windows("A.xlsx").visible = false, 并且，除了更长的加载时间之外，我A从B文件中打开文件没有任何问题或警告，并且完全更新.