关闭使用VBA在同一台计算机上由其他用户打开的excel工作簿 [英] Close an excel workbook opened by another user on the same computer using VBA

问题描述

我一直在使用excel文件制作Excel文档，使用excel 2010来完成我的工作。我们一直在使用的一个问题是用户可能会打开工作簿，并忘记在离开之前关闭工作簿。它们仍然登录到计算机（共享计算机），但该用户配置文件被锁定，另一个用户登录。使用中的文件存储在网络驱动器上，因此它可能被锁定在不同的计算机上，或同一台计算机上想要访问的当前用户正在使用。当然，如果工作簿保持打开状态，新用户不能进行更改。我想知道是否有办法向文档添加一些代码，所以我可以放一个按钮，关闭工作簿的另一个用户仍然打开的实例，也许给当前用户保存的选项，然后关闭并重新打开当前用户打开的工作簿，以便他们可以访问进行更改？让我知道，如果我需要澄清任何事情给你。谢谢！

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!

推荐答案

你可以做一些技巧。如果没有人更改了单元格的选择（如选择 A1 或另一个单元格），主要思想是关闭工作簿，例如10分钟。

You can do some trick. The main idea is to close workbook if nobody has changed the selection of cells (i.e. select A1 or another cell) in example for 10 minutes.

1）将此代码添加到vba模块中：

1) add this code to the vba module:

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）将以下代码添加到 ThisWorkbook module：

2) add following code to the ThisWorkbook module:

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

功能 closeWb 将每10分钟调用一次（ Application.OnTime Now + TimeSerial（0， 10，0），closeWb执行此操作），并检查上次选择是否超过10分钟。如果是，那么关闭wb。

Function closeWb will 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.

这篇关于关闭使用VBA在同一台计算机上由其他用户打开的excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！