Apllication.OnTime

[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

 

[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

 

[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

 

[Tom Ogilvy]

It has to remain open.

--
Regards,
Tom Ogilvy

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

 

[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).

 

[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

 

[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

 

[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

 

[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

 

[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