PDA

View Full Version : Solved: Run macro every day automatically?



griffism
04-23-2008, 12:47 PM
Hi there.

I have a macro that I have to run at 6 am every business day. It does everything for me from opening and changing the file to saving and closing it. All I have to do is get up early and hit run every morning. Is there a way to get this to happen automatically (so I can sleep in another hour)???

Thanks!

Simon Lloyd
04-23-2008, 12:51 PM
use the OnTime method like this:

Application.OnTime TimeValue("06:00:00"), "MyCode"

where MyCode is your code!

griffism
04-23-2008, 12:59 PM
Ok. I put that in a new subroutine (Run_Morning_Macro) and set it for a minute later and then hit run and it looks like it worked! So I assume I can run "Run_Morning_Macro" before I go home and if I leave my computer and excel on and up, it'll work?!


Thanks!!!!

Simon Lloyd
04-23-2008, 01:06 PM
You don't have to leave Excel open (i dont think) as it will open it at that time and run the macro, of course it will not close it again unless you state it to!

griffism
04-23-2008, 01:25 PM
Even better!!

Bob Phillips
04-23-2008, 02:59 PM
Simon is right, but two points. Your computer needs to be on (maybe obvious, but maybe not), and if you automatically set the next OnTime in the macro, you need to have a stop mechanism.

Simon Lloyd
04-23-2008, 04:26 PM
Bob, i didnt include a stop because i (maybe mistakenly) thought it didnt need one as he wanted this to occur every day so even if he closed the workbook the event would open it back up!
This should stop the timer.

Application.OnTime TimeValue("06:00:00"), Procedure:="MyMacro", Schedule:=False

Bob Phillips
04-24-2008, 12:28 AM
Nothing lasts for ever Simon.

Simon Lloyd
04-24-2008, 12:38 AM
Alas 'tis true young sir!

griffism
04-30-2008, 12:50 PM
I don't understand what the stop does. Are you saying it doesn't stop after it runs the called macro?

Also, I'm a she.

A new question was if the macro would run if I shut down my computer between when I hit run and restarted before the time I set.

Bob Phillips
04-30-2008, 12:57 PM
Yes of course it does, but each run would have to invoke tomorrow's run. This will go on forever, so if you wnat it to end at any time, you will have to cancel tomorrow's OnTime.

griffism
04-30-2008, 01:11 PM
I guess I was just doing that by commenting the line out. :)