on time

[Rich]

I need a macro to start once an hour on the hour. What are my options to do this and what is the best way? Thanks Rich

 

[Glaswegian]

Rich

Have a look at Chip Pearson's site for using the OnTime method.

Regards

 

[Rich]

On time sounds great, looked at the site. What I want is the code to have a macro run on the hour. Example: 0100 hrs., 0200 hrs., 0300 hrs. etc.

thanks for any help. I did not understand what to do from the other site.

 

[Smitty]

From the VBA helpfile:

OnTime Method Example

This example runs my_Procedure 15 seconds from now.

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
This example runs my_Procedure at 5 P.M.

Application.OnTime TimeValue("17:00:00"), "my_Procedure"
This example cancels the OnTime setting from the previous example.

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False

What's your starting and ending time to run your code? Can you post your code?

Hope that helps

Smitty

 

[Rich]

macro: thanks

Sub testcopyline()
'
' testcopyline Macro
' Macro recorded 1/28/2005 by

'
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
Sheets("Sheet2").Select
ActiveSheet.Unprotect
Sheets("Sheet1").Select
Range("D3:D32").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("65500:65500").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet1").Select
Range("C9").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveWorkbook.Save
End Sub

 

[Glaswegian]

Hi Rich

See if this helps. This goes in a standard module.

Public startTime As Double
Public Const cTimeIntervalMins = 60
Public Const cTheMacro = "MyMacro"

Sub StartTheClock()
startTime = Now + TimeSerial(0, cTimeIntervalMins, 0)
Application.OnTime earliesttime:=startTime, procedure:=cTheMacro, schedule:=True
End Sub

Sub MyMacro()
testcopyline
StartTheClock
End Sub


The public variables hold the name of the macro and the time interval. The sub 'StartTheClock' does what it says..it starts counting 60 minutes from the time the sub was run. So 60 minutes later it runs the sub 'MyMacro' which calls your macro. It also runs StartTheClock again, so that the countdown begins on the next 60 minutes. You might want to put the StartTheClock routine in the Workbook Open procedure so that it runs whenever you first open the workbook. As long as Excel is open, it will continue to run.

Does this help?

Regards

 

[Rich]

will give it a try. One question. will this always run it on the hour. It has to be exact every time , like I said. Rich

 

[Rich]

Hi, the code seems to make it work every 60 minutes, not on the hour. How do I write it for on the hour. Rich. Ex. 0100 hrs., 0200hrs. etc.

 

[Glaswegian]

Rich

The easy answer is to start the procedure on the hour - it will then run every 60 minutes thereafter. Otherwise I guess you would need to set up some kind of loop, with specific times in a range of cells. Not quite sure how that would work though.

 

[Rich]

Thanks for your help. I want exce ontime to reconize it by a set time. Again thanks for you help. Rich