Automatic macros
Posted by John Whitacre on December 05, 2001 5:39 AM
My company designs control systems for water and waste water treatment plants. I have a PC control system for one of my projects that will required that a excel report be printed out every midnight. I have figured out how to link the data from my control software to excel but i need to print the report out. I have written a macro that will print out the worksheet but only if you press ctrl-A. I also got it to print out at a certain time only if ctrl-a has been pressed before the print time is reached. How can I have this macro automatically running all the time, waiting for mid-night to print the report?
Posted by Damon Ostrander on December 05, 2001 8:59 AM
Hi John,
You can use the OnTime method of the Excel application object to schedule you macro to run a a particular time. Say your macro is named "PrintReport". To schedule the macro to run at midnight, run this macro:
Sub SchedulePrint()
Application.OnTime Date()+1, "PrintReport"
End Sub
Your PrintReport macro must be in a macro module rather than a worksheet or workbook event code area. The Date()+1 argument simply sets the time to run the macro to 00:00:00 tomorrow. And of course, as you mentioned, Excel and the workbook containing the macro will have to be open from the time you run SchedulePrint to midnight.
Keep that water clean.
Damon
