Scheduled Procedures

R

R Avery

Is there any way to determine what procedures are scheduled (with
Application.OnTime) to run and when? Or, supposing I already know the
procedure name, is there anyway to determine when it has been scheduled
for? Also, is there any way to completely erase all scheduled events?
Any help would be most appreciated.
 
C

Chip Pearson

There is no way to determine what has been scheduled with OnTime.
The only way unschedule an event is to use the *exact* scheduled
time in a call to OnTime with the Schedule argument set to false.
There is no way to clear all scheduled events short of closing
Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

R Avery

Chip said:
There is no way to determine what has been scheduled with OnTime.
The only way unschedule an event is to use the *exact* scheduled
time in a call to OnTime with the Schedule argument set to false.
There is no way to clear all scheduled events short of closing
Excel.
Click to expand...
Oh - If quitting Excel clears all scheduled procedures, is there anyway
to ensure that a procedure executes at a particular time, regardless of
how many times i have quit Excel?

Perhaps in my Personal.xls OnOpen Workbook event I could schedule the
event? Or would the API Timer function work regardless of whether or
not Excel was closed?
 
C

Chip Pearson

Once you quit Excel, all OnTime events are cleared. Therefore,
you would need to schedule an OnTime event in the workbook's open
code. This assumes that Excel will be open. You can use
Window's Scheduled Tasks tool to automatically open Excel at a
given time, and then in that workbook's Open code, run the code
or schedule an OnTime event.

I wouldn't recommend using Windows API timers (even though I
describe them on my web site) because they will cause Excel to
close immediately if Excel is in Edit mode (e.g., you are editing
the contents of a cell).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
R

R Avery

Suppose I do not use Windows Scheduled Tasks, and instead put the code
(below) in Personal.xls. Then, so long as I have Excel open (and
assuming I re-loaded Excel every day), the AutoSendEvents would occur
every day at 730pm. However, if I leave Excel open overnight and do not
re-open it, it will not run the scheduled event. Correct me if I am wrong.



Private Sub Workbook_Open()
Application.OnTime Now() - Time() + TimeSerial(19, 30, 0),
"AutoSendEvents"
End Sub



However, what happens if I have multiple instances of Excel open? Then,
each one could conceivably run the same procedure, no? What can I do to
ensure that only a single instance of Excel runs the procedure? My idea
is the following, but let me know if this idea is flawed or if there is
a better way. Presumably, the Personal.xls will have a maximum of one
writable instance open at a time.


public sub AutoSendEvents
if not thisworkbook.readonly then
' Perform code here
end if
end sub



Any thoughts would be appreciated.
 
Top