On Sep 30, 7:39*pm, Dave Peterson wrote:
Chip's code is pretty nice.
This line:
Public Const cRunIntervalSeconds = 60
is how you can change the interval.
since you want 600 seconds (10 minutes), you can use:
Public Const cRunIntervalSeconds = 600 '60*10
=========
This is untested, but it did compile.
Option Explicit
'So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
'and have it run every 10 minutes between those times, would this
'complete the needs of the timer
Public RunWhen As Double
Public Const cRunIntervalSeconds As Long = 600 '60 seconds * 10 minutes
Public Const cRunWhat As String = "SpreadRecordMacro1"
Public Const BeginTime As Date = #8:35:00 AM#
Public Const FinishTime As Date = #3:05:00 PM#
Sub Auto_Open()
* * *If Time < BeginTime Then
* * * * *'wait until that time
* * * * *Application.OnTime earliesttime:=BeginTime, _
* * * * * * *procedu=cRunWhat
* * *Else
* * * * *'just start it right now????
* * * * *Application.Run cRunWhat
* * *End If
End Sub
Sub Auto_Close()
* * *'if you're closing the workbook, then
* * *'stop the timer from reopening your workbook
* * *'and running the macro!!
* * *Call StopTimer
End Sub
Sub StartTimer()
* * *RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
* * *Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
* * * * *Schedule:=True
End Sub
Sub SpreadRecordMacro1()
* * *MsgBox "My Macro could go here"
* * *'600/60/24 is 10 minutes
* * *'so check to see if it's too late to schedule the next
* * *'run
* * *If Time (FinishTime - (cRunIntervalSeconds / 60 / 24)) Then
* * * * *'don't start it again.
* * *Else
* * * * *StartTimer
* * *End If
End Sub
Sub StopTimer()
* * *On Error Resume Next
* * *Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
* * * * *Schedule:=False
End Sub
==============
There's actually a small design flaw/bug in this code. *If your times got close
to midnight, then the comparisons could fail when you when want them to.
But I bet that won't affect you. *If you do start to work long hours, I'll leave
it to you to fix the problem!
On 09/30/2010 13:01, Kfletch wrote:
On Sep 30, 12:17 pm, *wrote:
On Sep 29, 9:05 pm, Dave *wrote:
I would take another look at Chip's instructions and code:http://www.cpearson.com/excel/OnTime.aspx
Then if you have specific questions about that code, post back. *(Read through
the "stopping a time" section. *Ignore the stuff after that.)
And for Chip's code, you can put all that stuff in a single module.
On 09/29/2010 15:09, Kfletch wrote:
Thanks in advance to anyone who can help me with this.
I have a simple excel macro written and want it to run every so often
(5 min / 10 min / 1 hour not sure yet) between the hours of 8:35:00 am
and 3:05:00 pm. I have been digging through related topics regarding
the OnTime method (here..http://www.cpearson.com/excel/ontime.htmand
here..http://www.ozgrid.com/Excel/run-macr...me.htmaswellas
many more) and cannot really get my head around the easiest way to do
this.
Most of my confusion comes from where to write the code whether it be
in my current macro module, or in new modules / where and how the
sub's and sub of sub's go. Also I have trouble determining what is
actual code and what is a field I must define from much of the
information that is out there.
It would be much appreciated if anyone could help an amateur.
--
Dave Peterson- Hide quoted text -
- Show quoted text -
OK, you are correct, I made my way into the process a bit and
determined that several of my questions were easily answered.
Now, for the actual code question. In this section for starting (and
stopping) the timer here...
Sub StartTimer()
* * *RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
* * *Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
* * * * *Schedule:=True
End Sub
I am not sure which portions of this are actual code that needs to
remain, and which portions I need to complete, also what format the
numbers need to be in.
So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
and have it run every 10 minutes between those times, would this
complete the needs of the timer
* * Sub StartTimer()
* * * * RunWhen = Now + TimeSerial(0,0,c600.00)
* * * * Application.OnTime EarliestTime:=08:35:00, Procedu=c"My
Macro Name", _
* * * * * * Schedule:=True
* * End Sub- Hide quoted text -
- Show quoted text -
After a little more work I have been able to get the macro running,
however I cannot determine where I input the time to start the timer,
and where to stop the timer. See full code below.
Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "SpreadRecordMacro1"
_____________________________________________
Sub StartTimer()
* * * RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
* * *Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
* * * * *Schedule:=True
End Sub
____________________________________________
Sub SpreadRecordMacro1()
' SpreadRecordMacro1 Macro
''
* * *"My Macro" .............
* * *StartTimer
End Sub
___________________________________________
Sub StopTimer()
* * *On Error Resume Next
* * *Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat, _
* * * * *Schedule:=False
End Sub
I have tried to input a time to start, and a time to stop. I must be
inputing in improperly or in the incorrect place. Any advice would be
much appreciated.
--
Dave Peterson- Hide quoted text -
- Show quoted text -
Dave;
I am very appreciative of your help. Your code is working wonderfully
for now and I will watch it a few days to confirm that it is stopping
and starting properly.
The only problem i am running into now is that I run about 5 different
excel models on my screens during the day. I switch back and forth
between them and just realized that the "my macro" (a simple copy :
Paste Special command) is running in whatever excel model I happen to
be in. What is the exact code and procedure (specifically for this
series of sub and sub macros) to ensure that the macro only runs in
the workbook I have the macro built in? In looking at the current
code, it would seem the time variables would not need to be workbook
specific, just "my Macro". I assume I would just need to add a line
before the first line in the "my macro" code that essentially states
"go to - a specific file, run the macro". I dont mind if it forces the
workbook I am working in to become inactive since, from my research,
having it work in the background on an inactive workbook seems
troublesome.
Best Regards