Application.ontime DateValue ???

[plo888]

I have an macro that runs several other macros using the ontime method.

Sub ontime()

Application.ontime TimeValue("5:25:00"), "daily1"
Application.ontime TimeValue("5:30:00"), "daily2"

Application.ontime DateValue("3/1/2011") + TimeValue("5:40:00"), "monthly"
Application.ontime DateValue("2/12/2011") + TimeValue("6:30:00"), "weekly"

End Sub

I want to write a code where the monthly and weekly are ran without me having to update the dates every week/month.
You see the monthly and weekly ontime method calls is hardcoded with a date and time and want to see the coding required for me to it run every Saturday morning for the weekly and every 1st of the month without ever having to update the ontime macro ever unless needed.

Please assist and thanks in advance.

 

[John_w]

Sub ontime()
    
    Application.ontime TimeValue("5:25:00"), "daily1"
    Application.ontime TimeValue("5:30:00"), "daily2"
    
    If Weekday(Now) = vbSaturday Then
        Application.ontime TimeValue("5:40:00"), "weekly"
    End If
    
    If Day(Now) = 1 Then
        Application.ontime TimeValue("6:30:00"), "monthly"
    End If
    
End Sub

This assumes that Sub ontime() is run once every day (before 5:25), as your code implies.

 

[plo888]

Actually, I run it once a week, but have to update the hardcoded dates once a week.
Instead I incorporated your codes, but created another macro called start_ontime

Sub start_ontime()

Application.ontime TimeValue("5:00:00"), "ontime"

End Sub

So, it looks like all i have to do is run start_ontime macro once and it'll trigger my ontime macro, which includes all my scheduled macros.

I did a test run and seems to have kicked off properly, but do you see anything that might be off?

 

[John_w]

I don't think start_ontime() is needed.

If you run ontime() only once a week, how can daily1() and daily2() be run every day, as their names imply? I'm assuming that, based on their names, daily1 and daily2 are meant to be run every day. Do they reschedule themselves to run the next day? For this to work you would need to leave open either the workbook or just Excel.

With my code, if you run ontime() only once a week, then weekly() is run only if you run ontime() on a Saturday. Similarly, monthly() is run only if you run ontime() on the 1st day of the month. That's why I said I assumed ontime was being run once every day.

One way is to use Windows task scheduler to open your workbook every day before 5:25 and call ontime() from the Workbook_Open event.

 

[plo888]

Unfortunately, because of our network restrictions, I cant use scheduler.

My goal is the write a macro so that I only need to execute the macro once and hope it runs smoothly for as long as I noticed any issues with the data.

Because I didnt know the coding to run Saturday only or first of the month only, i just improvised with the datevalue + timevalue. Hence I will need to change the dates once a week.

The daily will execute everyday when my ontime runs and doesnt require a daily execution.

My concern is that if I dont have a start_ontime, my weekly and monthly will not execute because, based on my experience, it requires an exeuction of the ontime to check for dates. The start_ontime will do that. I hope I was clear here.

btw john, thanks for the replies, greatly appreciated.

 

[John_w]

Because I didnt know the coding to run Saturday only or first of the month only, i just improvised with the datevalue + timevalue. Hence I will need to change the dates once a week.

The following code calculates the date of the next Saturday and the date of the 1st day of the next month (both from the current date), and shows them being used with the Application.OnTime method:

    Dim NextSaturdayDate As Date
    Dim NextMonth1stDate As Date
    
    NextSaturdayDate = Date - Weekday(Date) + vbSaturday - 7 * (vbSaturday <= Weekday(Date))
    NextMonth1stDate = DateSerial(Year(Date), Month(Date) + 1, 1)
    
    Application.OnTime NextSaturdayDate + TimeValue("6:30:00"), "weekly"
    Application.OnTime NextMonth1stDate + TimeValue("5:40:00"), "monthly"

 

[plo888]

here's my updated code.

Sub ontime2()
'would love to have this macro executed once a month with following codes

Application.ontime TimeValue("5:25:00"), "fc_rec_db6"
Application.ontime TimeValue("5:30:00"), "cusip4sedol_db6"
Application.ontime TimeValue("5:35:00"), "daily_db6"

'the above three executes daily

Application.ontime DateValue("2/26/2011") + TimeValue("7:30:00"), "short_interest_db6" 'this will execute on specific dates as this date changes often

Application.ontime DateValue("3/10/2011") + TimeValue("9:00:00"), "ERAM" 'this code executes every 10th day of the each month; john can you provide a more stable code

Dim NextTuesdayDate As Date
Dim NextFridayDate As Date
Dim NextSaturdayDate As Date
Dim NextMonth1stDate As Date

NextTuesdayDate = Date - Weekday(Date) + vbTuesday - 7 * (vbTuesday <= Weekday(Date))
NextFridayDate = Date - Weekday(Date) + vbFriday - 7 * (vbFriday <= Weekday(Date))
NextSaturdayDate = Date - Weekday(Date) + vbSaturday - 7 * (vbSaturday <= Weekday(Date))
NextMonth1stDate = DateSerial(Year(Date), Month(Date) + 1, 1)

Application.ontime NextTuesdayDate + TimeValue("6:30:00"), "weekly_m2m"
Application.ontime NextFridayDate + TimeValue("6:30:00"), "start_date"
Application.ontime NextSaturdayDate + TimeValue("6:30:00"), "weekly"
Application.ontime NextMonth1stDate + TimeValue("5:40:00"), "monthly"

End Sub