Im having a hard time with VBA editor, it shows me some errors sometimes, and sometimes don't. Mostly, I got error " Can't find project or library" and what i had done so far was deleting the error file and tried to recover the previous version of the file before. I could find any missing items from this suggestions. http://www.kb.blackbaud.co.uk/articles/Article/75344
And that really happened to multiple files and i have been busy for a stupid thing like this for a month already. Im really tired of how unstable excel is.
The script i have used got from this forum:
https://stackoverflow.com/questions/4743236/excel-timer-to-close-workbook
and it works totally fine BUT Excel sometimes gave me that error which i have no idea what to do.
So I would like to use Task Scheduler to do this job instead, is there anyway to do so? THanks:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Insert code below into "ThisWorkbook"
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">'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</code>Insert code below into a Module
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">'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</code>
</code></pre>
And that really happened to multiple files and i have been busy for a stupid thing like this for a month already. Im really tired of how unstable excel is.
The script i have used got from this forum:
https://stackoverflow.com/questions/4743236/excel-timer-to-close-workbook
and it works totally fine BUT Excel sometimes gave me that error which i have no idea what to do.
So I would like to use Task Scheduler to do this job instead, is there anyway to do so? THanks:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Insert code below into "ThisWorkbook"
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">'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</code>Insert code below into a Module
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">'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</code>
</code></pre>