使用 VBA 关闭同一台计算机上其他用户打开的 Excel 工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21222797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Close an excel workbook opened by another user on the same computer using VBA
提问by Jason247
I have been working on an excel document for making employee schedules for my work, using excel 2010. One issue that we constantly have is that a user may have the workbook open and forget to close it before they leave. They are still logged into the computer (a shared computer) but that user profile is locked, and another user logs in. The file in use is stored on a network drive, so it could be locked on different computers, or the same computer the current user that wants to access it is using. Of course, if the workbook was left open, the new user can't make changes. I was wondering if there is a way to add some code to the document so I could put a button that would close the instance of the workbook that is still open by another user, perhaps giving the current user the option to save it or not, then close and reopen the workbook that the current user opened so they can gain access to make changes? Let me know if I need to clarify anything for you. Thank you!
我一直在使用 excel 2010 编写一份 excel 文档，用于为我的工作制定员工时间表。我们经常遇到的一个问题是用户可能打开工作簿并在离开前忘记关闭它。他们仍然登录计算机（共享计算机），但该用户配置文件被锁定，而另一个用户登录。正在使用的文件存储在网络驱动器上，因此它可能被锁定在不同的计算机上，或同一台计算机上要访问它的当前用户正在使用。当然，如果工作簿保持打开状态，新用户将无法进行更改。我想知道是否有办法向文档添加一些代码，以便我可以放置一个按钮来关闭仍由另一个用户打开的工作簿实例，也许让当前用户可以选择是否保存它，然后关闭并重新打开当前用户打开的工作簿，以便他们可以访问进行更改？如果我需要为您澄清任何事情，请告诉我。谢谢！
回答by Dmitry Pavliv
You can do some trick. The main idea is to close workbook if nobody has changed the selection of cells (i.e. select A1or another cell) in example for 10 minutes.
你可以做点小动作。主要思想是如果A1在 10 分钟内没有人更改单元格的选择（即选择或另一个单元格），则关闭工作簿。
1) add this code to the vba module:
1) 将此代码添加到 vba 模块：
Public lastSelectionChange As Date
Sub closeWB()
If DateDiff("n", lastSelectionChange, Now) > 10 Then
ThisWorkbook.Close SaveChanges:=True
Else
Application.OnTime Now + TimeSerial(0, 10, 0), "closeWb"
End If
End Sub
2) add following code to the ThisWorkbookmodule:
2) 在ThisWorkbook模块中添加以下代码：
Private Sub Workbook_Open()
lastSelectionChange = Now
closeWB
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
lastSelectionChange = Now
End Sub
Function closeWbwill called every 10 minutes (Application.OnTime Now + TimeSerial(0, 10, 0), "closeWb"do this) and check if last selection change was over 10 minutes ago. If so, then close wb.
函数closeWb将每 10 分钟调用一次（Application.OnTime Now + TimeSerial(0, 10, 0), "closeWb"执行此操作）并检查上次选择更改是否在 10 分钟前。如果是，则关闭 wb。
回答by Pleun
That would be a very serious security issue in te operation system. Being able to interact with another users' files and running programs.
这将是操作系统中一个非常严重的安全问题。能够与其他用户的文件进行交互并运行程序。
However, you could have the current user do a "Save-as" or create a copy and continue from there
但是，您可以让当前用户执行“另存为”或创建副本并从那里继续
回答by Lance
I think I have a solution. You place a timer code for e.g. every 30s which searches a specific folder for a specific file/file name. If the file doesn't exist, then do nothing. If the file exists then it closes the workbook without saving changes. To start the shut down procedure on a peers PC that has this workbook open then you create a separate macro that creates that specific file and deletes it just before shutdown. You have to make sure that the shutdown procedure doesn't work for read only copies of the workbook. Alternatively you could manually paste the file there and delete once the master workbook is closed.
我想我有一个解决方案。您放置一个计时器代码，例如每 30 秒在特定文件夹中搜索特定文件/文件名。如果文件不存在，则什么都不做。如果文件存在，则关闭工作簿而不保存更改。要在打开此工作簿的对等 PC 上启动关闭程序，您可以创建一个单独的宏来创建该特定文件并在关闭前将其删除。您必须确保关闭过程不适用于工作簿的只读副本。或者，您可以手动将文件粘贴到那里，并在主工作簿关闭后删除。
