分类导航

程序问答发布时间：2022-06-02 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了Excel 中的宏每 30 分钟保存一次，并在 35 分钟没有使用后关闭。我需要卸载 ThisWorkbook，但不知道如何，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决Excel 中的宏每 30 分钟保存一次，并在 35 分钟没有使用后关闭。我需要卸载 ThisWorkbook，但不知道如何？

开发过程中遇到Excel 中的宏每 30 分钟保存一次，并在 35 分钟没有使用后关闭。我需要卸载 ThisWorkbook，但不知道如何的问题如何解决？下面主要结合日常开发的经验，给出你关于Excel 中的宏每 30 分钟保存一次，并在 35 分钟没有使用后关闭。我需要卸载 ThisWorkbook，但不知道如何的解决方法建议，希望对你解决Excel 中的宏每 30 分钟保存一次，并在 35 分钟没有使用后关闭。我需要卸载 ThisWorkbook，但不知道如何有所启发或帮助；

用于自动保存和在空闲工作时关闭工作簿的 VBA 代码。问题是，如果在代码关闭工作簿时打开了程序的另一个实例，Excel 会继续运行代码。我想我需要做的是卸载工作簿，但我不知道如何。我尝试过“卸载工作簿”、“卸载此工作簿”和“卸载重置计时器”[检测活动并启动 35 分钟计时器的模块]。我收到一个错误，提示 Workbook/ThisWorkbook/resetTimer 不是可以卸载的对象。我找不到可以卸载哪些对象的列表。

这是ThisWorkbook下的代码

Option Explicit

Private Sub ThisWorkbook_open()
    If ThisWorkbook.Readonly = false Then
    Application.onTime Now + TimeValue("00:30:00"),"SaveThis"
    End If

    If ThisWorkbook.Readonly = false Then
    Application.onTime Now + TimeValue("00:35:00"),"CloseDownfile"
    End If
End Sub

Private Sub ThisWorkbook_Close()  
    Unload ThisWorkbook
'    Unload resetTimer
End Sub

Private Sub ThisWorkbook_SheetCalculate(ByVal Sh As Object)
resetTimer
End Sub

Private Sub ThisWorkbook_SheetChange(ByVal Sh As Object,ByVal Target As RangE)
resetTimer
End Sub

Private Sub ThisWorkbook_SheetSELEctionChange(ByVal Sh As Object,ByVal Target As RangE)
resetTimer
End Sub

这是模块：

Public CloseDownTime As Variant

Public Sub resetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTimE) Then Application.onTime EarlIEstTime:=CloseDownTime,Procedure:="CloseDownfile",schedule:=false
CloseDownTime = Now + TimeValue("00:35:00") ' change as needed
Application.onTime CloseDownTime,"CloseDownfile"
End Sub

Public Sub CloseDownfile()
On Error Resume Next
ThisWorkbook.Close SaveChanges:=True
Unload ThisWorkbook
End Sub

Sub SaveThis()
    Application.displayAlerts = false
    ThisWorkbook.Save
    Application.displayAlerts = True

Application.onTime Now + TimeValue("00:30:00"),"SaveThis"
End Sub

解决方法

您的常规模块应该更像这样（见下文）。这将从您的 ThisWorkbook 模块中删除逻辑。

Option Explicit

Public CloseTime As Variant
Public SaveTime As Variant

Public Sub StartTimers()
    StartSaveTimer
    StartCloseTimer
End Sub
Public Sub CancelTimers()
    CancelSaveTimer
    CancelCloseTimer
End Sub

Sub StartSaveTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelSaveTimer 'remove any exisTing timer
    SaveTime = Now + TimeValue("00:30:00")
    Application.onTime SaveTime,"SaveThis"
End Sub
Sub CancelSaveTimer()
    On Error Resume Next
    Application.onTime EarliestTime:=SaveTime,Procedure:="SaveThis",schedule:=false
    On Error GoTo 0
End Sub

Sub StartCloseTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelCloseTimer 'remove any exisTing timer
    CloseTime = Now + TimeValue("00:35:00")
    Application.onTime CloseTime,"CloseThis"
End Sub
Sub CancelCloseTimer()
    On Error Resume Next
    Application.onTime EarliestTime:=CloseTime,Procedure:="CloseThis",schedule:=false
    On Error GoTo 0
End Sub

Public Sub CloseThis()
    On Error Resume Next
    CancelTimers
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub SaveThis()
    Application.DisplayAlerts = false
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    StartSaveTimer
End Sub

这是每 30 分钟保存一次并在 35 分钟没有使用后关闭的更正代码。感谢@TimWilliams 提供的所有帮助！

ThisWorkbook 下的代码：

Option Explicit

Private Sub Workbook_Open()
Call StartTimers
End Sub

Private Sub Workbook_Close()
CancelTimers
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
CancelCloseTimer
StartCloseTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object,ByVal Target As RangE)
CancelCloseTimer
StartCloseTimer
End Sub

Private Sub Workbook_SheetSELEctionChange(ByVal Sh As Object,ByVal Target As RangE)
CancelCloseTimer
StartCloseTimer
End Sub

模块代码：

Option Explicit

Public CloseTime As Variant
Public SaveTime As Variant

Public Sub StartTimers()
    StartSaveTimer
    StartCloseTimer
End Sub

Public Sub CancelTimers()
    CancelSaveTimer
    CancelCloseTimer
End Sub

Sub StartSaveTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelSaveTimer 'remove any exisTing timer
    SaveTime = Now + TimeValue("00:30:00") 'save frequency,change as needed
    Application.onTime SaveTime,"SaveThis"
End Sub

Sub CancelSaveTimer()
    On Error Resume Next
    Application.onTime EarliestTime:=SaveTime,schedule:=false
    On Error GoTo 0
End Sub

Sub StartCloseTimer()
    If ThisWorkbook.ReadOnly Then Exit Sub
    CancelCloseTimer 'remove any exisTing timer
    CloseTime = Now + TimeValue("00:35:00") 'idle time before closing,change as needed
    Application.onTime CloseTime,"CloseThis"
End Sub

Sub CancelCloseTimer()
    On Error Resume Next
    Application.onTime EarliestTime:=CloseTime,schedule:=false
    On Error GoTo 0
End Sub

Public Sub CloseThis()
    On Error Resume Next
    CancelTimers
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub SaveThis()
    Application.DisplayAlerts = false
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    StartSaveTimer
End Sub