I have written a macro that uses Application.OnTime that works if I manually execute the macro. I'm trying to automate this process so I don't have to write Application.OnTime in "This Workbook" or (Private Sub Workbook_Open() Most of you do this because you can have windows scheduler open the workbook at a certain time which starts the macros on open. I CANNOT USE SCHEDULER.
Because I am not able to use windows scheduler I will keep the workbook open and the timer should refresh my data then Call "my Macro" at a certain time everyday.
Where do I place this code, and how do I set an auto timer?
You could create a kind of recurrence procedure. It could look as follows:
Sub Call_At_3_30()
'first call appropriate procedure
Call myProcedure
'next, set new calling time
Application.OnTime TimeValue("3:30:00"), "Call_At_3_30"
End Sub
Somewhere you will keep your main procedure, it this situation:
Sub myProcedure
'your code here
End Sub
In this situation you need to run first subroutine Call_At_3_30
only once. But you need to remember that Excel must be turned on all the time.
Optionally, if you want to call your procedure after 24 hours you could change .OnTime
instruction in this way:
Application.OnTime Now + 1, "Call_At_3_30"
Some other modifications are possible, too.