Run macro on set time

[joelnichols]

I have found a few posts on this but couldnt adapt it for what i wanted exactly. I need a code to open a WB and run a macro at a certain time each day, then save and close the book. Example:

1. Open book at 12:01am
2. Run Macro1 for example
3. Save book
4. Close book
5. Repeat next day at 12:01am

Actually I have 3 macros to run but I think I can figure out how to string them together when I get to that point.

Thx in advance

 

[HalfAce]

This should do what you want to do if you put it in say the Workbook_Open event of your workbook. (Paste the code in, save & close the workbook, then open it back up & close it again.)

Application.OnTime TimeValue("00:01:00"), "Macro1"

You should be able to call your second & third macros from Macro1 and then save & close the book.
You don't need any code to open the workbook. As Long as the Excel application does not get closed down, the workbook will (if it's not already open) open itself up to execute the code.

Hope it helps.

 

[joelnichols]

I tried this and notta...

Private Sub Workbook_Open()
Application.OnTime TimeValue("02:30:00"), "Macro4"


End Sub

XL uses Windows clock I assume? I set this at 2:28 and at 2:31 nothing had happened. This is so simple yet I can even screw it up

 

[HalfAce]

After installing the code, did you save, close and then reopen the workbook?

[EDIT:]
Yes. The system clock is what is being used.

 

[joelnichols]

ADVERTISEMENT

Yes several times. Does it matter where the macro is in the book?


EDIT:

Nevermind..I moved the macro and got it to work. Didnt know it was looking for it in a certain spot. Thanks for the help

 

[HalfAce]

Oh yeah. Sorry. I suppose I should've been a bit more descriptive on where the code goes.

Good job figuring it out.
(I believe you've advanced beyond your signature quote!)

 

[joelnichols]

ADVERTISEMENT

Considering the time I have spent working in XL I should be way beyond where I am. Thanks for the vote of confidence tho

 

[mezzy01]

Run macro at given time

I'm trying to get this bit of code to work but no joy

Private Sub Workbook_Open()
Application.OnTime TimeValue("02:30:00"), "Macro4"


End Sub

Where does it need to be placed on the book. I've put it at the top, saved and closed the worksheet but still no joy

Please help

 

[joelnichols]

Try putting the macro (macro4) in a module if you havent already.

And put this in "Thisworkbook"

Private Sub Workbook_Open()
Application.OnTime TimeValue("02:30:00"), "Macro4"

Then save and close the book and reopen it before the time its suppose to start. That resets the whole deal. After that it should run at 2:30 everyday as long as Excel is running even if the book is closed.