PDA

View Full Version : Solved: Schedule a macro



cactus
09-15-2007, 10:45 AM
Hi,

In a book that will be permanently open, I need to schedule a macro to run each 30 minutes between two specifics hours (6 am and 11 pm).
Thanks and sorry for my english.

Cactus.-

malik641
09-15-2007, 12:04 PM
Hi Cactus,

I would check out Chip Pearson's website (http://www.cpearson.com/Excel/OnTime.aspx) to learn about the .OnTime method.

I believe the following code will do what you ask. All you have to do is remove the comments from the following lines:
'Application.OnTime EarliestTime:=dblRunWhen, _
' Procedure:=strRunWhat, _
' Schedule:=True

And you have to change the variable strRunWhat to whatever the procedure name is that you want to execute.

Here is the code:
Option Explicit
Option Private Module

Const strRunWhat = "The_Procedure_Name"

Public Sub SetUpMacroSchedule()
' Sets up a schedule for a specific macro(s) to be run
Dim i As Long
Dim dblCurrentTimeInLoop As Double ' To stop the loop at 11:00 PM
Dim dblRunWhen() As Double

' Set i=1 to start the array counter
i = 1

' Start time at 6:00 AM (relative to the Computer System Time)
ReDim dblRunWhen(0)
dblRunWhen(0) = DateSerial(Year(Now()), Month(Now()), Day(Now())) _
+ TimeSerial(6, 0, 0)

dblCurrentTimeInLoop = dblRunWhen(0)
' FOR TESTING PURPOSES
Debug.Print DateSerial(Year(dblRunWhen(0)), Month(dblRunWhen(0)), Day(dblRunWhen(0))) _
+ TimeSerial(Hour(dblRunWhen(0)), Minute(dblRunWhen(0)), Second(dblRunWhen(0)))

' Create a loop to set up the array of timed tasks.
' Loop's condition is to continue looping until the time
' of dblCurrentTimeInLoop is greater than 11:00 PM
Do While (TimeSerial(Hour(dblCurrentTimeInLoop), _
Minute(dblCurrentTimeInLoop), 0) < TimeSerial(23, 0, 0))
ReDim Preserve dblRunWhen(i)
' Set the next dblRunWhen to the next half-hour
dblRunWhen(i) = dblRunWhen(i - 1) + TimeSerial(0, 30, 0)

' FOR TESTING PURPOSES
Debug.Print DateSerial(Year(dblRunWhen(i)), Month(dblRunWhen(i)), Day(dblRunWhen(i))) _
+ TimeSerial(Hour(dblRunWhen(i)), Minute(dblRunWhen(i)), Second(dblRunWhen(i)))

'Application.OnTime EarliestTime:=dblRunWhen, _
' Procedure:=strRunWhat, _
' Schedule:=True

dblCurrentTimeInLoop = dblRunWhen(i)

' Increment i
i = i + 1

Loop

End Sub
As it is now, it will print the schedule in the Immediate window for you (just to be sure that the code works correctly). Test it and let us know if it produces the correct schedule for you :)

Good luck!

cactus
09-15-2007, 01:38 PM
It works perfectly!!!

Thank you very much

malik641
09-15-2007, 02:09 PM
Your welcome :hi:

And welcome to VBAX!

malik641
09-15-2007, 02:29 PM
Oh yeah. Don't forget to mark your thread "Solved" by using the thread tools inside the thread :thumb

Zack Barresse
09-16-2007, 03:39 PM
One large caveat of this is you must have the file open. As you say the file will always be open, this could lead to problems. Such as what if the file closes? If this would ever be an issue for you, setup a vbs file to run with Windows Scheduler (assuming you have a Windows OS) at your desired times. An example of this can be found at this running thread ...

http://forums.techguy.org/business-applications/619660-excel-help.html

HTH

Bob Phillips
09-16-2007, 04:03 PM
If it isn't open, it will open when it's time is due, as long as Excel is running.

cactus
09-17-2007, 03:40 PM
Hi,

I`ll try to use the Windows Scheduler and an AutoOpen maybe (?)...It looks easiest and strongest for a beginer like me.
Anyway the solution porpoused by malik641 was very useful.

Thanks again

Zack Barresse
09-18-2007, 10:20 AM
If it isn't open, it will open when it's time is due, as long as Excel is running.
Hence the possible problem. ;)

If it is a non-issue to the OP, kudos, but it needs to be addressed.