Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
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 Avery" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
Just to add, never schedule more than one event and save the time you used
for it in a global variable. Have the scheduled event schedule the next event. See Chip's page for a workable approach http://www.cpearson.com/excel/ontime.htm -- Regards, Tom Ogilvy "Chip Pearson" wrote in message ... 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 Avery" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
Chip Pearson wrote:
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. 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
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 Avery" wrote in message ... Chip Pearson wrote: 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. 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scheduled Procedures
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating sub procedures | Excel Discussion (Misc queries) | |||
ListBox Procedures | Excel Programming | |||
Sequential Sub Procedures | Excel Programming | |||
what's wrong with this sub procedures? | Excel Programming | |||
Splitting Procedures | Excel Programming |