Application.Ontime ?

[zone709]

Hi,

I been researching this today but i am getting stuck. I would like to run a Sub () in my personal workbook called Sub T205 () on Fridays at 6pm eastern time.

Not sure what Application.Ontime to use and where to put it into. Can I put it into my personal workbook to run that sub? Do I have to put it into Thisworkbook. Just not sure and been adding some

Application.Ontime to workbook, but nothing is happening. Any help would be great.​

 

[zone709]

I added this in my sheet into ThisWorkBook to test it. Even though I need it Fridays and a time. I tried testing this for now.

Private Sub Workbook_Open()
    Application.OnTime TimeValue("16:47:00"), "T205"
End Sub

Sub T205 () is in my personal book in Module 6. When it hits the time message comes up cannot run macro the macro C:\Users\frank\desktop\Book1.xlsm'!T205. The macro may not be available in this workbook etcc

I even placed T205 () macro in Thisworkbook. Still same error. I know I am doing something wrong, but cant figure out what.

 

[zone709]

hi so after researching this i got it to work. I had to pu the private sub in workbook and the t205 macro in a module in that book and it worked.

I need help again though to have this run on a Monday and Friday at 15:00:00

[LEFT][COLOR=#333333][FONT=monospace]Private Sub Workbook_Open()
    Application.OnTime TimeValue("16:47:00"), "T205"
End Sub[/FONT][/COLOR][/LEFT]

How do I put that in this code for that to work. Any help thanks

 

[zone709]

Still trying to get this. I look it up and not much comes up. I think I need something before the time stating a certain day to trigger this. Friday's at 16:47:00. If I can't get this then I can figure out how to add mondays.

 

[John_w]

hi so after researching this i got it to work. I had to pu the private sub in workbook and the t205 macro in a module in that book and it worked.

I need help again though to have this run on a Monday and Friday at 15:00:00

[LEFT][COLOR=#333333][FONT=monospace]Private Sub Workbook_Open()
    Application.OnTime TimeValue("16:47:00"), "T205"
End Sub[/FONT][/COLOR][/LEFT]

How do I put that in this code for that to work. Any help thanks

Try this code instead of yours:

ThisWorkbook module:

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

Module1 (standard module):

Option Explicit

Public NextRunTime As Date
Public Const cRunProc = "T205"  'the name of the procedure to run

Public Sub StartTimer()

    Dim currentTime As Date, currentDate As Date
    
    currentTime = Now
    currentDate = Int(currentTime)
    
    If Format(currentTime, "hh:mm:ss") < "15:00:00" Then
        NextRunTime = Application.WorksheetFunction.Min(currentDate + (8 - Weekday(currentDate, vbMonday)) Mod 7, _
                                                        currentDate + (8 - Weekday(currentDate, vbFriday)) Mod 7) + TimeValue("15:00:00")
    Else
        NextRunTime = Application.WorksheetFunction.Min(currentDate + 1 + (8 - Weekday(currentDate + 1, vbMonday)) Mod 7, _
                                                        currentDate + 1 + (8 - Weekday(currentDate + 1, vbFriday)) Mod 7) + TimeValue("15:00:00")
    End If

    Application.OnTime EarliestTime:=NextRunTime, Procedure:=cRunProc, Schedule:=True
    
    MsgBox "The next run of " & cRunProc & " has been scheduled for " & Format(NextRunTime, "Dddd dd/mm/yyyy hh:mm:ss")
    
End Sub


Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=NextRunTime, Procedure:=cRunProc, Schedule:=False
End Sub


Public Sub T205()
    MsgBox "Hello " & Now
   
    'Schedule this procedure again
    StartTimer
End Sub

The StartTimer routine schedules the next run of T205 on Monday at 15:00:00 or Friday at 15:00:00, whichever is soonest, with respect to the currentTime variable. Note that you can test the calculation of NextRunTime for different dates and times by replacing the currentTime = Now line with a specific date and time like this:

    currentTime = Date + 1 + TimeValue("14:00:00")

which sets the currentTime to tomorrow at 14:00:00. If you do this then comment out the Application.OnTime line because that looks at the computer's clock.

 

[zone709]

First thing thanks for the response. I see now from your code that it was a little more then I thought it would be and thank you for the help. I see this as a start timer () Will this work if my excel is closed or only work when I open it as I have now Private, Because I can just run a schedule task to open the excel sheet on a Monday morning and Friday morning. as I wouldn't know another way, but I thought I read something that can trigger a private to trigger the macro after closing excel. not sure if that's possible.

 

[John_w]

The procedure scheduled by Application.OnTime will only run if the workbook or the Excel application is open at the scheduled time. If you close the macro workbook while an OnTime event is pending, but leave the Excel application open, Excel will re-open that workbook to execute the procedure and will not close the workbook after the OnTime event is finished. See http://www.cpearson.com/excel/ontime.aspx for more details.

If you have a scheduled task to open the workbook on Mondays and Fridays then my code should still work. Whether you or the scheduled task opens the workbook, the T205 macro will be scheduled to run at the next required time, depending on the current time.

 

[zone709]

The procedure scheduled by Application.OnTime will only run if the workbook or the Excel application is open at the scheduled time. If you close the macro workbook while an OnTime event is pending, but leave the Excel application open, Excel will re-open that workbook to execute the procedure and will not close the workbook after the OnTime event is finished. See http://www.cpearson.com/excel/ontime.aspx for more details.

If you have a scheduled task to open the workbook on Mondays and Fridays then my code should still work. Whether you or the scheduled task opens the workbook, the T205 macro will be scheduled to run at the next required time, depending on the current time.

Yeah thought so thanks again for the help. ?

 

[John_w]

The procedure scheduled by Application.OnTime will only run if the workbook or the Excel application is open at the scheduled time. If you close the macro workbook while an OnTime event is pending, but leave the Excel application open, Excel will re-open that workbook to execute the procedure and will not close the workbook after the OnTime event is finished. See http://www.cpearson.com/excel/ontime.aspx for more details.

If you have a scheduled task to open the workbook on Mondays and Fridays then my code should still work. Whether you or the scheduled task opens the workbook, the T205 macro will be scheduled to run at the next required time, depending on the current time.

Just a correction to the BIB. In my code the Workbook_BeforeClose cancels the scheduled OnTime event, therefore Excel will not reopen the workbook because an OnTime event is no longer pending. The BIB is true only if the scheduled OnTime event is not cancelled.

 

[zone709]

Just a correction to the BIB. In my code the Workbook_BeforeClose cancels the scheduled OnTime event, therefore Excel will not reopen the workbook because an OnTime event is no longer pending. The BIB is true only if the scheduled OnTime event is not cancelled.

I seen that at the end thanks.