使用VBA关闭同一台计算机上另一个用户打开的excel工作簿

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文档。我们经常遇到的一个问题是用户可能打开工作簿而忘记在他们离开之前关闭它。它们仍然登录到计算机(共享计算机),但该用户配置文件已锁定,另一个用户登录。正在使用的文件存储在网络驱动器上,因此可以锁定在不同的计算机上,也可以锁定在同一台计算机上想要访问它的当前用户正在使用。当然,如果工作簿保持打开状态,则新用户无法进行更改。我想知道是否有办法在文档中添加一些代码,这样我就可以放一个按钮来关闭另一个用户仍然打开的工作簿实例,也许给当前用户提供保存或不保存的选项,然后关闭并重新打开当前用户打开的工作簿,以便他们可以访问进行更改?如果我需要为您澄清一切,请告诉我。谢谢!

3 个解决方案

#1

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.

你可以做一些技巧。如果没有人在例子中改变细胞的选择(即选择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 ThisWorkbook module:

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

函数closeWb将每10分钟调用一次(Application.OnTime Now + TimeSerial(0,10,0),“closeWb”执行此操作)并检查上次选择更改是否超过10分钟。如果是这样,那么关闭wb。

#2

That would be a very serious security issue in te operation system. Being able to interact with another users' files and running programs.

这将是te操作系统中非常严重的安全问题。能够与其他用户的文件进行交互并运行程序。

However, you could have the current user do a "Save-as" or create a copy and continue from there

但是,您可以让当前用户执行“另存为”或创建副本并从那里继续

#3

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上启动关闭过程,您需要创建一个单独的宏来创建该特定文件,并在关闭之前将其删除。您必须确保关闭过程不适用于工作簿的只读副本。或者,您可以手动将文件粘贴到那里,并在关闭主工作簿后删除。

#1