vba 如何阻止 Excel 提示重新打开工作簿？

How to stop Excel from prompting to reopen a workbook?

提问by Joe

I have a readonly Excel workbook containing a VBA application. The application saves any data that needs to be saved in a database, and the workbook is always closed without saving (by setting ThisWorkbook.Saved = True in BeforeClose).

我有一个包含 VBA 应用程序的只读 Excel 工作簿。应用程序保存任何需要保存在数据库中的数据，工作簿总是关闭而不保存（通过在 BeforeClose 中设置 ThisWorkbook.Saved = True）。

I have the following problem:

我有以下问题：

• User double-clicks on the workbook in Windows Explorer, workbook opens.

• User double-clicks a second time on the workbook in Windows Explorer.

• Excel prompts: "MyWorkbook.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen MyWorkbook.xls?"

• If the user clicks on "Yes", the workbook is reopened without executing the BeforeClose event handler of the instance that was already open.

• 用户在 Windows 资源管理器中双击工作簿，工作簿打开。

• 用户在 Windows 资源管理器中的工作簿上再次双击。

• Excel 提示：“MyWorkbook.xls 已打开。重新打开将导致您所做的任何更改被丢弃。要重新打开 MyWorkbook.xls？”

• 如果用户单击“是”，则会重新打开工作簿，而不执行已打开实例的 BeforeClose 事件处理程序。

This is a problem in my application, because it means that some important cleanup code in the BeforeClose event handler does not get executed.

这是我的应用程序中的一个问题，因为这意味着 BeforeClose 事件处理程序中的一些重要清理代码没有得到执行。

Can anyone suggest a VBA solution to this. Which could be:

任何人都可以为此提出 VBA 解决方案。这可能是：

• Suppressing the prompt to reopen the workbook. Instead silently use the already-open instance.

• Somehow get the BeforeClose or some other event handler to run in the original instance before it is closed, so I can run my cleanup code.

• 禁止重新打开工作簿的提示。而是默默地使用已经打开的实例。

• 以某种方式让 BeforeClose 或其他一些事件处理程序在关闭之前在原始实例中运行，这样我就可以运行我的清理代码。

Update:

更新：

This is Excel 2003.

这是 Excel 2003。

I can get rid of the unwanted prompt by setting "ThisWorkbook.Saved = True" in the Workbook_SheetChanged event handler (the VBA app is responsible for saving any data that needs to be saved in a database, so I don't care about having Excel saving changes).

我可以通过在 Workbook_SheetChanged 事件处理程序中设置“ThisWorkbook.Saved = True”来摆脱不需要的提示（VBA 应用程序负责保存需要保存在数据库中的任何数据，所以我不关心有 Excel保存更改）。

However this doesn't solve my problem: if I do this, then double-clicking on the Workbook in explorer silently reopens the workbook, but still does this without calling by "BeforeClose" event handler.

但是，这并不能解决我的问题：如果我这样做，则在资源管理器中双击工作簿以静默方式重新打开工作簿，但仍会在不调用“BeforeClose”事件处理程序的情况下执行此操作。

So to rephrase the question:

所以改写这个问题：

• Is there anyway using VBA to detect and intercept a workbook being reopened in this way?

• 无论如何使用VBA来检测和拦截以这种方式重新打开的工作簿？

Update 2

更新 2

Accepting BKimmel's answer - it does seem there is no VBA way to intercept this event from within the workbook.

接受 BKimmel 的回答 - 似乎没有 VBA 方法可以从工作簿中拦截此事件。

The solution I'll implement will be to move the application code into an XLA add-in, which is automatically loaded (if not already loaded) when the workbook is loaded. The add-in can handle Open and BeforeClose events, and store the information it needs to do the clean up.

我将实施的解决方案是将应用程序代码移动到 XLA 加载项中，该加载项在加载工作簿时自动加载（如果尚未加载）。加载项可以处理 Open 和 BeforeClose 事件，并存储进行清理所需的信息。

采纳答案by BKimmel

Man...this seemseasy but it is a tough one. I toyed around with every event and application/workbook property that I could think of in my 7 or so years of experience working with Excel VBA and I couldn't come up with anything elegant at all... I think the short answer is that there is not really a short elegant solution to this problem (If there is, I would really like to see it). That's the bad news.

The good news is that there I think there areways to work around the problem...1 is a little bit more elegant, but would require you to rethink some of your methods and the other is dirty, but probably a little bit simpler.

The more elegant waywould involve rethinking and refactoring your methods...I think the reason I've never run into this problem in my work is that at the end of each of my methods, the workbook is left in a "good" state... or in other words, if there is "cleanup" to do then it is done at the end of each method. If there is a huge overhead performance hit in this, you might consider setting a boolean flag and restricting whether or not it is run at the end of each of your other methods. (i.e. runCleanup = ReturnTrueIfCleanupIsNeeded() If (runCleanup = true) Then CleanupMethod() End If)

The dirtier waywould involve making the entire workbook itself into a kind of "ad-hoc mutex" by
1) copying the entire (original) Workbook at the WorkBookOpen event into another(temp) WorkBook and using the "SaveAs" method on the tempto place it in a seperate location.
2) Storing the (original) path.
3) Adding code to the WorkBook open event that checks for the (temp) and either A) Closes itself immediately and activates tempor B) Overwrites itself with (temp) and then uses SaveAs
4) Catch the event before the user saves the WorkBook and save it to boththe originallocation, and the templocation.
5) At the WorkBookClose event, run any cleanup you need to and save the tempback to its original location.

The point of all this is to side-step the "collision" that happens when you open the workbook twice - essentially, by creating a tempworkbook when you open it the first time and saving it in a different location (maybe you could even hide the file?) you are ensuring that when the user clicks the original, it won't collide with the data they are already working with in the application. (Of course, this has it's own problems/questions like "What if I don't know what paths the user has access to save the temp to" or "What if the user opens the tempXLS file...but that's why I called it the dirtway)"

I know that's a lot, especially if you aren't accustomed to working with VBA; If you want me to post some code to help you with any of those steps, leave a comment and I will.

Thanks for the interesting question.

伙计……这看起来很容易，但它很难。在我 7 年左右的 Excel VBA 工作经验中，我尝试了我能想到的每一个事件和应用程序/工作簿属性，但我根本想不出任何优雅的东西......我认为简短的答案是这个问题没有一个真正简短的优雅解决方案（如果有，我真的很想看看）。这就是坏消息。

好消息是，我认为有一些方法可以解决这个问题……1 更优雅一点，但需要您重新考虑一些方法，而另一种方法很脏，但可能更简单一点。

更优雅的方式将涉及重新思考和重构你的方法......我认为我在工作中从未遇到过这个问题的原因是在我的每个方法结束时，工作簿都处于“良好”状态......或者换句话说，如果有“清理”要做，那么它会在每个方法结束时完成。如果在这方面有巨大的开销性能损失，您可以考虑设置一个布尔标志并限制它是否在每个其他方法的末尾运行。（即 runCleanup = ReturnTrueIfCleanupIsNeeded() If (runCleanup = true) Then CleanupMethod() End If）

更脏的方法是将整个工作簿本身变成一种“临时互斥锁”
temp) WorkBook 并在temp上使用“SaveAs”方法将其放置在单独的位置。
2）存储（原始）路径。
3) 将代码添加到工作簿打开事件以检查 ( temp) 和 A) 立即关闭并激活temp或 B) 用 ( temp)覆盖自身然后使用 SaveAs
4) 在用户保存工作簿之前捕获事件并将其保存到双方的原始位置，以及临时位置。
5) 在 WorkBookClose 事件中，运行您需要的任何清理并将临时文件保存回其原始位置。

所有这一切的重点是避免在您打开工作簿两次时发生的“冲突” - 本质上，通过在您第一次打开它时创建一个临时工作簿并将其保存在不同的位置（也许您甚至可以隐藏文件？）您确保当用户单击原始文件时，它不会与他们已经在应用程序中使用的数据发生冲突。（当然，这有它自己的问题/问题，例如“如果我不知道用户有权将临时保存到什么路径怎么办”或“如果用户打开临时XLS 文件怎么办……但这就是我的原因称之为土路）”

我知道这很多，特别是如果您不习惯使用 VBA；如果你想让我发布一些代码来帮助你完成这些步骤中的任何一个，请发表评论，我会的。

谢谢你的有趣问题。

回答by Matthew Rathbone

You can use some special application events to handle when a workbook is reopened and manually fire the Workbook_Close code:

您可以使用一些特殊的应用程序事件来处理重新打开工作簿并手动触发 Workbook_Close 代码：

Create a class module called ApplicationController with the following code:

使用以下代码创建一个名为 ApplicationController 的类模块：

Public WithEvents CApp As Application
Public CurrentWB as Workbook

Private Sub CApp_WorkbookOpen(ByVal wb As Excel.Workbook)


Dim sPathName As String
sPathName = wb.Name
if sPathName = CurrentWB.Name then CallSomeMethod()

End Sub

then on your workbook_load event do something like:

然后在您的 workbook_load 事件上执行以下操作：

Public controller as new ApplicationController
Private Sub Workbook_Open()
set controller.CApp = Excel.Application
set controller.CurrentWB = ThisWorkbook
End Sub

now when excel goes to open a new workbook you'll capture the event and run your safety method. You could even prevent it reopening the workbook if you wished.

现在，当 excel 打开一个新工作簿时，您将捕获该事件并运行您的安全方法。如果您愿意，您甚至可以阻止它重新打开工作簿。

回答by Vijay

You can also suppress the prompt by:

您还可以通过以下方式抑制提示：

This will reopen an open file without the warnings.. but note that any changes are discarded!

这将在没有警告的情况下重新打开一个打开的文件......但请注意，任何更改都将被丢弃！

Application.DisplayAlerts = False
Workbooks.Open (sPath)
Application.DisplayAlerts = True

From Excel Forum

来自Excel 论坛

This solution worked for my needs

此解决方案适合我的需求