Need code to run a macro on a specific day of week and a specific time

[kk1990]

Hi All,

Can someone help me with a code to run a macro on every Thursday of week at 1.00PM and 4 PM


Thanks & Regards!

 

[Luke M]

Are you going to leave XL open all the time? If so, you can use the:
Application.OnTime (SpecifyTime,"NameOfMacro")
See VB help file for more details.

If not, you'd need to setup a scheduled task on whatever computer you are using.

 

[kk1990]

Are you going to leave XL open all the time? If so, you can use the:
Application.OnTime (SpecifyTime,"NameOfMacro")
See VB help file for more details.

If not, you'd need to setup a scheduled task on whatever computer you are using.

@Luke M

Thanks.. But this only specifies the time and will trigger macro everyday.
I need the macro to be triggered on specific day also (thursday)

Regards!

 

[Luke M]

The date is part of the time. When I say "Thursday 1 pm", that is a time.
You can use VB to figure out when the next time call should be. Here's a more fleshed out version.

Sub StartTimer()
'This is the macro you run the first time
Call WhatToDo

'Note that this application call is self-referencing
'This will cause a loop to start after you run the first time
Application.OnTime NextTime, "StartTimer"
End Sub

Private Sub WhatToDo()
'This is the macro being called
MsgBox "Hello"
End Sub

Function NextTime() As Date
Dim curTime As Date
Dim curHour As Long
Dim newHour As Long
Dim curDay As Long
Dim daysAdd As Long

curTime = Now
curDay = Int(curTime)
curHour = Hour(curTime)
extraDay = 0
If Weekday(curDay, vbThursday) = 1 Then
    'If today is thursday, check if we need to be at 4 pm
    If curHour >= 13 And curHour < 16 Then
        newHour = 16
    Else
        newHour = 13
    End If
    'If it's Thursday and before 4 pm, don't go to next week
    If curHour < 16 Then curDay = curDay - 7
Else
    'If not thursday, next will be at 1 pm
    newHour = 13
End If

'Find out how many days to add
daysAdd = 8 - Weekday(curDay, vbThursday)

NextTime = curDay + daysAdd + TimeSerial(newHour, 0, 0)
End Function