4

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Shayne K
  • 69
  • 1
  • 2
  • 10
  • to be sure- you will keep Excel application open all the time? is there any constant time interval between each macro call? can you start it manually for the first time? – Kazimierz Jawor Jun 25 '13 at 15:50
  • The workbook will be open all the time, never closing. Interval will be the same. (at 3:30 everyday) if I were to start it manually one time at 3:30 is there a way for it to keep calling the macro everyday...24 hours later? – Shayne K Jun 25 '13 at 16:02

1 Answers1

6

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.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • This works perfect. I added the code from above and the macro executes perfectly but but I'm getting the error message Cannot run the macro Code: Sub Call_At_9_43() 'first call appropriate procedure Call Mail_Sheet_Outlook_Body 'next, set new calling time Application.OnTime TimeValue("9:49:00"), "Call_At_9_49" End Sub I know my "sub call" doesn't match, but that shouldn't be an issue. Will this popup error prevent it from running tomorrow at 9:49 if I don't click ok. I'm thinking another call with send.keys to hit enter – Shayne K Jun 25 '13 at 16:54
  • 1
    I would need to see the complete code... (you could edit your question). `Cannot run the macro` suggest that you put wrong name of the calling sub. Error occurs during `Sub Call_at_9_43()` execution or after, as a result of `Application.OnTime`? – Kazimierz Jawor Jun 25 '13 at 17:01
  • You are correct, to test that theory, just now I wrote this. #1= no error #2= error message Sub Call_At_10_00() End Sub 'first call appropriate procedure Call Mail_Sheet_Outlook_Body 'next, set new calling time Application.OnTime TimeValue("10:00:00"), "Call_At_10_00" Sub Call_At_9_43() Call Mail_Sheet_Outlook_Body Application.OnTime TimeValue("10:01:00"), "Call_At_10_49" End Sub – Shayne K Jun 25 '13 at 17:02
  • to be clear this is correct Sub Call_At_10_00() End Sub 'first call appropriate procedure Call Mail_Sheet_Outlook_Body 'next, set new calling time Application.OnTime TimeValue("10:00:00"), "Call_At_10_00" I hope it runs tomorrow. – Shayne K Jun 25 '13 at 17:03