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!
use the OnTime method like this:
[VBA]
Application.OnTime TimeValue("06:00:00"), "MyCode"
[/VBA]where MyCode is your code!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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!!!!
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!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
Even better!!
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
[VBA]
Application.OnTime TimeValue("06:00:00"), Procedure:="MyMacro", Schedule:=False
[/VBA]
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
Nothing lasts for ever Simon.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Alas 'tis true young sir!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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.
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I guess I was just doing that by commenting the line out.
Posting Permissions
Reply With Quote
