Application.OnTime TimeValue

[Krikkers]

Hello

I have written a macro for work that needs to execute every weekday.
It works well, but every time that I'm not in the office, it don't execute.
So for example if i'm off on Wednesday, Thursday morning it didn't run.
Also, I don't know how to include that it only runs on a weekday.
I have written this in ThisWorkbook:

Private Sub Workbook_Open()
Application.OnTime TimeValue("06:00:00"), "Macro"
End Sub

Someone that can help me?

Thanks a lot!

 

[Sixthsense]

So for example if i'm off on Wednesday, Thursday morning it didn't run.

On those days whether the file (system) will be running or turned off?

 

[Krikkers]

On those days whether the file (system) will be running or turned off?

When I'm out of office on Wednesday, then the macro do run on Wednesday morning, but not on Thursday morning.
So it seems that it only runs once at 6 o'clock, except when I reopen Excel. Then the macro will run the next morning.

 

[Andrew Poulsom]

If you want to leave the workbook unattended you will need to call the OnTime method again, at the end of your "Macro". Currently it's called only once when you open the workbook.

 

[Sixthsense]

In the end of Macro Code you have to reset the timer once again so that it will be kept on excel memory to trigger the code when the time hits.

Sub Macro()

    'Your code goes here
    Application.OnTime TimeValue("06:00:00"), "Macro"

End Sub

 

[Krikkers]

In the end of Macro Code you have to reset the timer once again so that it will be kept on excel memory to trigger the code when the time hits.

Sub Macro()

    'Your code goes here
    Application.OnTime TimeValue("06:00:00"), "Macro"

End Sub

Thanks! I'll try that.
But how can I force that it only runs on weekdays?

 

[Sixthsense]

But how can I force that it only runs on weekdays?

Sub Macro()

Select Case Format(Date, "DDD")
    Case Is = "Mon", "Tue", "Wed", "Thu", "Fri"
        'Your Code Goes Here....
End Select

Application.OnTime TimeValue("06:00:00"), "Macro"

End Sub

 

[Krikkers]

Sub Macro()

Select Case Format(Date, "DDD")
    Case Is = "Mon", "Tue", "Wed", "Thu", "Fri"
        'Your Code Goes Here....
End Select

Application.OnTime TimeValue("06:00:00"), "Macro"

End Sub

Thanks a lot!!

 

[Krikkers]

It worked perfectly, thanks!