跳到主要内容

在特定的空闲时间后如何自动保存和关闭Excel文件？

在某些情况下，例如将Excel文件保留在共享磁盘上，当用户仍在打开该文件时，其他人将无法编辑工作簿。 那么，如何在空闲一段时间后自动关闭文件以减轻他人的工作负担？ 本文将帮助您使用VBA方法。

闲置一定时间后，使用VBA代码自动保存并关闭Excel文件

闲置一定时间后，使用VBA代码自动保存并关闭Excel文件

要在一定的空闲时间后自动保存并关闭Excel文件，请执行以下操作。

1.打开所需的工作簿，使其自动保存，并在一定的空闲时间后关闭。 然后按 其他 + F11 钥匙一起打开 Microsoft Visual Basic应用程序 窗口。

2.在 Microsoft Visual Basic应用程序 窗口，请双击 的ThisWorkbook 在右窗格中打开“代码”窗口。 然后将下面的VBA代码复制并粘贴到“代码”窗口中。 看截图：

VBA代码1：在特定的空闲时间后自动保存并关闭Excel文件

Dim xTime As String
Dim xWB As Workbook

Private Sub Workbook_Open()
'Updated by Extendoffice 2019/1/20
    On Error Resume Next
    xTime = Application.InputBox("Please specify the idle time:", "KuTool For Excel", "00:00:20", , , , , 2)
    Set xWB = ActiveWorkbook
    If xTime = "" Then Exit Sub
    Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    If xTime = "" Then Exit Sub
    Reset
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If xTime = "" Then Exit Sub
    Reset
End Sub

Sub Reset()
    Static xCloseTime
    If xCloseTime <> 0 Then
        ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , False
    End If
    xCloseTime = Now + TimeValue(xTime)
    ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , True
End Sub

3.然后继续单击 插页 > 模块，然后将以下代码复制并粘贴到“模块”窗口中。 看截图：

VBA代码2：在特定的空闲时间后自动保存并关闭Excel文件

Sub SaveWork1()
'Updated by Extendoffice 2019/1/20
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    Application.DisplayAlerts = True
End Sub

4。 按 其他 + Q 同时关闭按键 Microsoft Visual Basic应用程序 窗口。

5。 点击 文件 > 另存为 > 浏览。 看截图：

6.在 另存为 对话框中，请选择一个文件夹来保存文件，在“文件名”框中根据需要命名，然后选择 Excel启用宏的工作簿 来自 除上文所 类型 下拉列表，最后单击 保存 按钮。 看截图：

从现在开始，每次打开此工作簿时， Kutools for Excel 对话框将会弹出。 请输入保存和关闭工作簿所基于的时间，然后单击 OK 按钮。

在指定的空闲时间后，工作簿将自动保存并关闭。 看截图：

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (30)
No ratings yet. Be the first to rate!
Sirlei Jose
Sirlei Jose
This comment was minimized by the moderator on the site
Hello good afternoon! I would like MSBox not to appear when opening the file. I don't want them to change the configured closing time.
Reply
0
0
Manuel
Manuel     Sirlei Jose
This comment was minimized by the moderator on the site
The Problem is it`s a file with links and every time you start it, it will ask you if you would update it and read only mode?
Reply
0
0
Manuel
Manuel
This comment was minimized by the moderator on the site
Hello
Code works but if i close the file before the time runs out, It will open it again and close. how can i stop the code when i close the file manual.
Reply
0
0
crystal
crystal     Manuel
This comment was minimized by the moderator on the site
Hi,

I tested the code and it did not reproduce the problem you mentioned. Can I ask which version of Excel you are using?
Reply
Report
0
0
Manuel
Manuel     crystal
This comment was minimized by the moderator on the site
Hi
It is Microsoft Excel 365 MSO (Version 2308) 32 bit.
Reply
0
0
Manuel
Manuel     Manuel
This comment was minimized by the moderator on the site
This is my Code:

Dim xTime As String
Dim xWB As Workbook

Private Sub Workbook_Open()
On Error Resume Next
xTime = "00:00:35"
Set xWB = Workbooks("Produktionsbericht - Kopie.xlsm")
If xTime = "" Then Exit Sub
Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
If xTime = "" Then Exit Sub
Reset
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If xTime = "" Then Exit Sub
Reset
End Sub

Sub Reset()
Static xCloseTime
If xCloseTime <> 0 Then
xWB.Application.OnTime xCloseTime, "SaveWork1", , False
End If
xCloseTime = Now + TimeValue(xTime)
xWB.Application.OnTime xCloseTime, "SaveWork1", , True
End Sub
Reply
0
0
Manuel
Manuel
This comment was minimized by the moderator on the site
Hello
All works at my file but i have a new Problem.
If i close the file before the time runs out, it will open it again and close it when the time runs out.
Can i stop the VBA Code when i close the File myself?
Reply
0
0
Chinna
Chinna
This comment was minimized by the moderator on the site
Code is not working if the module is protected, is there any way to get the code run if module is protected.
Reply
0
0
crystal
crystal     Chinna
This comment was minimized by the moderator on the site
Hi Chinna,

If the module is protected, the code is hidden and cannot be run. Sorry for the inconvenience.
Reply
Report
0
0
Moises Peraza
Moises Peraza
This comment was minimized by the moderator on the site
Perdonen, si se pudiera modificar el código o adaptarlo para que reaccione con formularios (UserForm), porque solo reacciona al estar activo en las celdas pero no en los formularios, gracias
Reply
0
0
crystal
crystal     Moises Peraza
This comment was minimized by the moderator on the site
Hi Moises Peraza,
UserForm does not affect the use of the VBA code. Can you describe the problem you encountered more clearly.
Reply
Report
0
0
Moisés Peraza
Moisés Peraza     crystal
This comment was minimized by the moderator on the site
Gracias por responder, el inconveniente que tengo es: le pongo de tiempo de inactividad 2 min. En el momento de trabajar con formularios (ingresar información del cliente), pasado ese tiempo me cierra o me tira el mensaje que se cerrara, espero darme a entender
Reply
0
0
Zakariya
Zakariya
This comment was minimized by the moderator on the site
Hi mister, how to view the countdown?
Reply
Report
0
0
Zakariya
Zakariya
This comment was minimized by the moderator on the site
Hi mister, how to view the countdown?
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
(Sigh.)
Have a look at the code.
If you put this VBA into Excel001.xlsm, but, when the timeout occurs, you have Excel002.xlsm as the "active" workbook, then it's actually going to close THAT Excel file, rather than the one you've put the VBA into.
Reply
0
0
Mike B
Mike B     Mike
This comment was minimized by the moderator on the site
look below, Mark's comment : "I would recommend identifying the workbook with the workbook file name. This would allow the users to have multiple workbooks open and closing out only the specific workbook that needs to have the time lock.

Suggestion: Set xwb = Workbooks("<file name>")
instead of: Set xwb = ActiveWorkbook

Also, replace all references to "ActiveWorkbook" to the Xwb in the module

Great code otherwise."
Reply
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
Hi. I need to set my shared spreadsheet on the office network to auto save and close when I'm done with it for the day. Only my boss and I are supposed to make and save changes, but several managers view it daily.
I need to be able to have it auto save for the two of us, while not saving for other users. Is this possible? Can I set the macro to work only for us? Or will it effect everyone?
I know how to protect the sheet, but I can't do that, because they want to be able to make some temporary changes from time to time. I just need to be able restrict auto save to specific users.
Reply
0
0
Garth
Garth
This comment was minimized by the moderator on the site
Is there a way to change this so it prompts the user to save/close the file, instead of automatically saving and closing the file?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations