vba Excel - 关闭工作簿的计时器

Excel - timer to close workbook

提问by Jay Kidd

Quite a while ago I whipped up (or found) some code to automatically close a shared workbook after a period if the user had left it open (e.g. overnight or all day). The code works well, except for when it closes the workbook within it resides; it also closes all workbooks and excel as well (without an Application.Quit). The users are becoming anoyed at this, does anyone know how I can get it to only close (Thisworkbook), not all the others?

不久前，如果用户将共享工作簿保持打开状态（例如，一夜之间或一整天），我会编写（或找到）一些代码来在一段时间后自动关闭共享工作簿。代码运行良好，除了关闭它所在的工作簿时；它还关闭所有工作簿和 excel（没有 Application.Quit）。用户对此感到厌烦，有谁知道我如何才能让它只关闭（Thisworkbook），而不是所有其他的？

Thanks.

谢谢。

Code below:

代码如下：

Option Explicit

' Declarations
Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Private mlngTimerID As Long

' start the timer
Public Sub StartTimer(lngInterval As Long)
   mlngTimerID = SetTimer(0, 0, lngInterval, AddressOf TimerCallBack)
End Sub

' when the timer goes off
Public Sub TimerCallBack(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal dwTime As Long)

 ' stop the timer
 StopTimer

  ' don't save if read only
 If ThisWorkbook.ReadOnly = False Then

  ' save
  ThisWorkbook.Save

 End If

 ' exit without saving
 ThisWorkbook.Activate
 ThisWorkbook.Close False

End Sub

Public Sub StopTimer()
   KillTimer 0, mlngTimerID
End Sub

'To use timer:
'To start the timer
'Call startTimer(1000)'1000 = 1 Second
'To stop timer
'Call stopTimer

回答by Corbin

I know this is an older question but I thought I'd share a resolution that works for me. Upon opening, the workbook is stored as a Public variable so that it will be the only workbook closed when the timer expires. If the workbook is closed before time expires, then the timer is cancelled. If the timer expires and the workbook is still open, then it will be saved and closed automatically.

我知道这是一个较旧的问题，但我想我会分享一个对我有用的解决方案。打开时，工作簿将存储为公共变量，以便在计时器到期时它将成为唯一关闭的工作簿。如果工作簿在时间到期之前关闭，则计时器将被取消。如果计时器到期并且工作簿仍处于打开状态，则它将自动保存并关闭。

Insert code below into "ThisWorkbook"

将下面的代码插入“ThisWorkbook”

'When the workbook is opened, call StartTimer()
Public Sub Workbook_Open()
    Run "StartTimer"
End Sub

'Detect if the workbook is closed
Public Sub Workbook_BeforeClose(Cancel As Boolean)
    'Cancel Saveclose
    Run "StopTimer"
End Sub

Insert code below into a Module

将下面的代码插入到模块中

'Global variables
Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' seconds (set to 5 minutes)
Public Const cRunWhat = "SaveClose"  ' the name of the procedure to run
Public GlobalBook As Workbook

'Start Timer using interval set in global variables
Sub StartTimer()
    Set GlobalBook = ActiveWorkbook
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

'Stop the Timer whenever the workbook is closed prematurely
Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

'Close the workbook automatically once the Timer has expired
Public Sub SaveClose()
    'Time is up, workbook will save and close automatically
    Dim wb As Workbook
    For Each wb In Workbooks
        'Check to see if workbook is still open
        If wb.Name = GlobalBook.Name Then
            Set wb = Application.Workbooks(GlobalBook.Name)
            'Close workbook and Save Changes
            wb.Close SaveChanges:=True
        End If
        Next
End Sub

回答by Tim Williams

Have you tried using Excel's "OnTime" instead ?

您是否尝试过使用 Excel 的“OnTime”？

http://msdn.microsoft.com/en-us/library/aa195809(v=office.11).aspx

http://msdn.microsoft.com/en-us/library/aa195809(v=office.11​​).aspx

Tim

蒂姆