Apllication.OnTime

M

Mischa Browne

Goodday,

You can use "OnTime" method to schedule a macro to run at a specific time.

Is there also a method to schedule a macro to run at and/or after a specific
date (system-date)?



TKs,
Mischa
 
P

Peter Beach

Hi Mischa,

If you give a full date/time OnTime will work to that date. Something like:

Application.OnTime Date + 1 + Time(12,0,0), "MyRoutine"

will run at noon tomorrow.

HTH

Peter Beach
 
M

Mischa Browne

Dear Peter,





Thanks, but does this mean I have to keep the workbook (that contains the
macro) active until tomorrow.

Or can I just open the workbook, tomorrow and then the macro will run?





MRegards,

Mischa
 
T

Tom Ogilvy

It has to remain open.

--
Regards,
Tom Ogilvy

Mischa Browne said:
Dear Peter,





Thanks, but does this mean I have to keep the workbook (that contains the
macro) active until tomorrow.

Or can I just open the workbook, tomorrow and then the macro will run?





MRegards,

Mischa
Click to expand...
 
T

Tom Ogilvy

Of course you could write code to save the scheduled date and time and
reinitiate the ontime event using the workbook_open event. But just setting
Ontime does not persist when you close excel. (I guess I should have said
you need to keep excel open - I don't believe you need to keep that specific
workbook open).
 
M

Mischa Browne

So actually,

There is no code which allows a macro to run at a specific date?

Fe Monday July 19 2004 at or after 1 pm


Adios,
Mischa
 
C

Chip Pearson

Mischa,

Try

Application.OnTime DateSerial(2004,7,19) + TimeSerial(13,0,0),
"MacroName"

Note that Excel must remain open from the time you execute the
OnTime macro until the scheduled time.


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

Vic Eldridge

Mischa,

I think the best way would be to use the Windows Task Scheduler
(Start > Programs > Accessories > System Tools > Scheduled Tasks)
to open a workbook that contains a Workbook_Open macro.

Regards,
Vic Eldridge
 
M

Mischa Browne

Can a user of the application bypass this option?

I want to protect my macro not to be used after a certain date.

Tks
Regards,
Mischa
 
P

Peter Beach

Hi Mischa,

Why not use something like:

Sub ExpiringMacro()
If Now > DateSerial(2004,12,31) Then Exit Sub
. . . other code
End Sub

IOW simply stop the macro from running after the expiry date.

You could obviously get more sophisticated if you wanted to.

Regards,

Peter Beach
 
Top