Running Macros at timed intervals
Posted by Allan on May 21, 2000 8:58 PM
Does anyone know how to create a macro so it runs at specific times throughout the day? I need a macro to update information on manufacturing activities every 4 hrs?
Posted by Celia on May 22, 2000 2:22 AM
Try to do what you want by putting the following in a module.
To test it, you can change the TimeValue from 04:00:00 (4 hours) to 00:00:05 (5 seconds)
Dim nextRun As Date
'Run this macro to start "YourMacro"
'This automatically runs "YourMacro" every 4 hours
nextRun = Now + TimeValue("04:00:00")
Application.OnTime nextRun, "ReRun"
'PUT YOUR CODE HERE
'Turns off the OnTime event when closing the file
'Run this to turn off the OnTime event
On Error Resume Next
Application.OnTime nextRun, "ReRun", schedule:=False
Posted by Celia on May 30, 2000 4:57 AM
This message could not be delivered to the address you supplied, so I'm posting it here :-
Replace YourMacro (in the two places it appears) with Macro1.
The five macros have to be in the same workbook. They can still work if they are in different workbooks, but the code would have to be changed.
To start the automatic running of Macro1 every 4 hours, the macro named StartMacro has to be run first (after opening the workbook). Instead, if you want the auto-run process to start automatically as soon as the workbook is opened, change the macro named StartMacro to Auto_Open.
An explanation of each macro is :-
StartMacro - Run this to kick-start the auto-run process
ReRun - This sets the OnTime event to run Macro1 every 4 hours
Macro1 - This is your macro that you want to run every 4 hours
Auto_Close - Turns off the OnTime event (otherwise the workbook will be opened every 4 hours)
StopMacro - Run this if you want to keep the workbook open but want to switch off the automatic updating. You can re-instate the auto-updating by running StartMacro
If you are still unable to get it working, mail me your workbook so I can check whats wrong.
Posted by Allan on June 04, 2000 9:28 PM
I tried this and I'm getting an error message.
I can run the Start Macro and the macro runs ok and it also runs when I start the rerun macro with a button click but when it comes time to have it run automatically it comes up with an error message, "Abiguous file: Rerun"
Does this makes sense?