Run a macro at a cretin time of day

[sd]

Can a macro be set up to run at a specific time? I have a macro I want
to run if it is 2:58pm can this be done? I think excel needs to be open
in order to do this but I am not sure if a macro can run with out an
actual event.

[Kaak]

Application.OnTime (.....

[K Dales]

Yes, with the OnTime method. The following would run the sub called MySub at
2:45 pm:
Application.OnTime TimeValue("14:45:00"), "MySub"

--
- K Dales


"sd" wrote:

> Can a macro be set up to run at a specific time? I have a macro I want
> to run if it is 2:58pm can this be done? I think excel needs to be open
> in order to do this but I am not sure if a macro can run with out an
> actual event.
>
>

[Nigel]

Use the application.ontime command. eg....

Application.OnTime TimeValue("14:58:00"), "your_macro_name"

--
Cheers
Nigel



"sd" <[email protected]> wrote in message
news:[email protected]...
> Can a macro be set up to run at a specific time? I have a macro I want
> to run if it is 2:58pm can this be done? I think excel needs to be open
> in order to do this but I am not sure if a macro can run with out an
> actual event.
>

[Martijn]

Open your excel, open with Alt-F11 the visual basic editor, Open Help
look for method: OnTime.

Put the code + My_Procedure in the personal macro book.

some examples :
My_procedure will start after 15 seconds from now
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

or at 17.00
Application.OnTime TimeValue("17:00:00"), "my_Procedure"

or to cancel it:
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False

I think this can do the trick.

Good luck with it. Let me know if it's working.
grtx's Martijn


"sd" <[email protected]> schreef in bericht
news:[email protected]...
> Can a macro be set up to run at a specific time? I have a macro I want
> to run if it is 2:58pm can this be done? I think excel needs to be open
> in order to do this but I am not sure if a macro can run with out an
> actual event.
>

[bpeltzer]

I've taken a different approach... Create the macro as an Auto_Open() macro,
or invoke it on the Workbook_Open event. Then set up the Excel file
containing the macro as a Windows Scheduled Task.

"sd" wrote:

> Can a macro be set up to run at a specific time? I have a macro I want
> to run if it is 2:58pm can this be done? I think excel needs to be open
> in order to do this but I am not sure if a macro can run with out an
> actual event.
>
>

[Martijn]

Good approach but your macro starts only when you open the workbook not on a
certain time.

You are right when you say "if excel is not open than the other macro's
don't work also"

grtx's Martijn


"bpeltzer" <[email protected]> schreef in bericht
news:[email protected]...
> I've taken a different approach... Create the macro as an Auto_Open()
macro,
> or invoke it on the Workbook_Open event. Then set up the Excel file
> containing the macro as a Windows Scheduled Task.
>
> "sd" wrote:
>
> > Can a macro be set up to run at a specific time? I have a macro I want
> > to run if it is 2:58pm can this be done? I think excel needs to be open
> > in order to do this but I am not sure if a macro can run with out an
> > actual event.
> >
> >

[bpeltzer]

The task scheduler allows me to indicate when the workbook should be opened
and, therefore, when the macro runs. So Excel doesn't need to be running;
it's kicked off at the designated time by the scheduler.


"Martijn" wrote:

> Good approach but your macro starts only when you open the workbook not on a
> certain time.
>
> You are right when you say "if excel is not open than the other macro's
> don't work also"
>
> grtx's Martijn
>
>
> "bpeltzer" <[email protected]> schreef in bericht
> news:[email protected]...
> > I've taken a different approach... Create the macro as an Auto_Open()
> macro,
> > or invoke it on the Workbook_Open event. Then set up the Excel file
> > containing the macro as a Windows Scheduled Task.
> >
> > "sd" wrote:
> >
> > > Can a macro be set up to run at a specific time? I have a macro I want
> > > to run if it is 2:58pm can this be done? I think excel needs to be open
> > > in order to do this but I am not sure if a macro can run with out an
> > > actual event.
> > >
> > >
>
>
>

[Gordon Rainsford]

Won't you have a problem if the workbook is already open?

Gordon Rainsford


bpeltzer <[email protected]> wrote:

> The task scheduler allows me to indicate when the workbook should be opened
> and, therefore, when the macro runs. So Excel doesn't need to be running;
> it's kicked off at the designated time by the scheduler.
>
>
> "Martijn" wrote:
>
> > Good approach but your macro starts only when you open the workbook not on a
> > certain time.
> >
> > You are right when you say "if excel is not open than the other macro's
> > don't work also"
> >
> > grtx's Martijn
> >
> >
> > "bpeltzer" <[email protected]> schreef in bericht
> > news:[email protected]...
> > > I've taken a different approach... Create the macro as an Auto_Open()
> > macro,
> > > or invoke it on the Workbook_Open event. Then set up the Excel file
> > > containing the macro as a Windows Scheduled Task.
> > >
> > > "sd" wrote:
> > >
> > > > Can a macro be set up to run at a specific time? I have a macro I want
> > > > to run if it is 2:58pm can this be done? I think excel needs to be open
> > > > in order to do this but I am not sure if a macro can run with out an
> > > > actual event.
> > > >
> > > >
> >
> >
> >


--
Gordon Rainsford
London

[bpeltzer]

Sure; there are trade-offs everywhere. My approach fails if the workbook is
open; the other fails if it isn't. But since I typically schedule these
things to run overnight, having the files open isn't usually an issue.
Besides, the macro files are special-purpose and wouldn't normally be open
except when I want them to run. And I love coming arriving at work with lots
of my reports already completed ;-)

"Gordon Rainsford" wrote:

> Won't you have a problem if the workbook is already open?
>
> Gordon Rainsford
> London
>

[Dave Peterson]

If you haven't looked at Chip Pearson's notes about OnTime, you may want to
review them:
http://www.cpearson.com/excel/ontime.htm

sd wrote:
>
> Can a macro be set up to run at a specific time? I have a macro I want
> to run if it is 2:58pm can this be done? I think excel needs to be open
> in order to do this but I am not sure if a macro can run with out an
> actual event.

--

Dave Peterson