Running macros at set times

[asjmoron]

Morning,

I have the below code to fire a macro at set times in the day to coincide with a datalink that refreshes just before. However, I have to close and re-open the workbook every morning to start the sequence again. Is there a way to keep these times on a loop to ensure that I can leave the book open indefinately and the macros still fire?

Private Sub Workbook_Open()


    Application.OnTime TimeValue("09:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("10:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("11:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("12:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("13:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("14:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("15:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("16:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("17:05:05"), "OpenLatestFile"

End Sub

 

[jmacleary]

Hi there. You could just wrap around like this:

Private Sub Workbook_Open()

looper:

    Application.OnTime TimeValue("09:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("10:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("11:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("12:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("13:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("14:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("15:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("16:05:05"), "OpenLatestFile"
    Application.OnTime TimeValue("17:05:05"), "OpenLatestFile"
GoTo looper
End Sub

 

[asjmoron]

Thanks @jmacleary I'll give that a go.

I did try using

DO
my code
LOOP

But im not sure if that would work?

 

[jmacleary]

Yes that would work.

 

[asjmoron]

Hey. I just tested both options but all it seems to is try to run the whole process immediatley. I need it to still run incrementally at the times then start again

 

[offthelip]

The way to do it is to use a scheduler which call itself at appropriate time something like this ( untested so I have probably got some syntax wrong)

Sub nextcall()
Call OpenLatestFile
timenow = Now()
If timenow > TimeSerial(17, 0, 0) Then
runwhen = Now + TimeSerial(14, 0, 0)
  Else
runwhen = Now + TimeSerial(1, 0, 0)
End If
Application.OnTime TimeValue(runwhen, "nextcall")
End Sub

 

[jmacleary]

yes ofthelip is right- I was being dim.