Run a Macro at a Set Time

[caritx]

Hi All,

I hope you can save me today. I am trying to run http://www.ozgrid.com/Excel/run-macro-on-time.htm in excel 07 but I can't seem to make it work and I am ready to cry.

In Thisworkbook code I have the following:
Private Sub Workbook_Open()
Application.OnTime TimeValue("17:00:00"), "MyMacro"

End Sub

In regular modules I have this:

Sub MyMacro()
'
' Macro5 Macro
'
Application.OnTime

TimeValue ("17:00:00"), "MyMacro"


Call Bank_format_print

End Sub

 

[John_w]

Don't know why that example has two OnTime's, as only one is necessary and to compound the confusion the 2nd one is a syntax error.

No need for the MyMacro subroutine. Just have:

Private Sub Workbook_Open()
Application.OnTime TimeValue("17:00:00"), "Bank_format_print"
End Sub

 

[maxx_daddy]

I have a similar report that I run everyday at work but the problem is you have to leave Excel open for the macro to run and if you are not at your desk or forget it then you miss the report. Instead, I use the Windows task scheduler, I created an entire different worksheet that opens the source file, copies the data, pastes it into the new workbook prints and closes everything (unless excel was open, then it just closes that workbook)

 

[tweedle]

@Maxx; I'm learning that more and more as a general guideline - anytime there are schedules and/or multiple workbooks envolved, having a master workbook controlling everything really seems to be the best/cleanest method to control (and debug) everything. I'm thinking I'll make that a normal paradigm.

 

[caritx]

Thank you so much I will try that in the morning!

 

[caritx]

Don't know why that example has two OnTime's, as only one is necessary and to compound the confusion the 2nd one is a syntax error.

No need for the MyMacro subroutine. Just have:

Private Sub Workbook_Open()
Application.OnTime TimeValue("17:00:00"), "Bank_format_print"
End Sub

This still isn't working.

Private Sub Workbook_Open()
'
' Macro5 Macro
'
Application.OnTime TimeValue("09:21:00"), "Bank_format_print"


'
End Sub


I tried without excel open and with excel and the file open and nothing.

 

[John_w]

Where are you putting the Workbook_Open code? It should go in the ThisWorkbook module. Your Bank_format_print subroutine should go in a standard module. Set the TimeValue string to a time in the future. Save and close the workbook, then reopen it _before_ the TimeValue time. Or run Workbook_Open from the VB Editor and then there is no need to close and reopen. The Workbook must be open and the time not elapsed for Bank_format_print to run.

To see whether Bank_format_print is being called, try putting the following statement as its first line:

MsgBox "Bank_format_print " & Now

 

[caritx]

Thanks it worked!