Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
Does anyone have any suggestions on how to set macro to run every 15 minutes?
Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
See:
http://www.ozgrid.com/Excel/run-macro-on-time.htm -- Regards Dave Hawley www.ozgrid.com "Eric" wrote in message ... Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
Stolen from a very nearby universe, er, answer to a question, and modified to
protect the guilty. Option Explicit Dim nextRunTime Sub startRunning() 'you could put ' Run "MyCode" 'in the Workbook_Open() event ' MyCode End Sub Sub stopRunning() On Error Resume Next Application.OnTime nextRunTime, "MyCode", , False End Sub Sub MyCode() nextSecond = Now + TimeValue("00:15:00") Application.OnTime nextSecond, "MyCode" 'your process to run right in with this stuff 'it has already set itself to run again in 15 minutes End Sub "Eric" wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
Chip Pearson's site explains it:
http://www.cpearson.com/excel/OnTime.aspx Eric wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
For using Application.OnTime, do I need to close the excel application in
order to stop the process? It seems to me when the file is closed without quiting the excel application, it will keep running the macro again. Does anyone have any suggestions? Thank everyone very much for any suggestions Eric "JLatham" wrote: Stolen from a very nearby universe, er, answer to a question, and modified to protect the guilty. Option Explicit Dim nextRunTime Sub startRunning() 'you could put ' Run "MyCode" 'in the Workbook_Open() event ' MyCode End Sub Sub stopRunning() On Error Resume Next Application.OnTime nextRunTime, "MyCode", , False End Sub Sub MyCode() nextSecond = Now + TimeValue("00:15:00") Application.OnTime nextSecond, "MyCode" 'your process to run right in with this stuff 'it has already set itself to run again in 15 minutes End Sub "Eric" wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
And it can do that. Have you checked out the link that Dave Peterson
provided below? One way to handle the closing of the workbook is to put the same code that is in Sub stopRunning into the workbook's _BeforeClose() event processor, or simply call stopRunning from there, as: Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "stopRunning" End Sub Remember that the Workbook_BeforeClose() code has to be placed into the ThisWorkbook code module. "Eric" wrote: For using Application.OnTime, do I need to close the excel application in order to stop the process? It seems to me when the file is closed without quiting the excel application, it will keep running the macro again. Does anyone have any suggestions? Thank everyone very much for any suggestions Eric "JLatham" wrote: Stolen from a very nearby universe, er, answer to a question, and modified to protect the guilty. Option Explicit Dim nextRunTime Sub startRunning() 'you could put ' Run "MyCode" 'in the Workbook_Open() event ' MyCode End Sub Sub stopRunning() On Error Resume Next Application.OnTime nextRunTime, "MyCode", , False End Sub Sub MyCode() nextSecond = Now + TimeValue("00:15:00") Application.OnTime nextSecond, "MyCode" 'your process to run right in with this stuff 'it has already set itself to run again in 15 minutes End Sub "Eric" wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
Thank everyone very much for suggestions
There is an error, does anyone have any suggestions on how to fix it? Thanks in advance for any suggestions Eric Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "stopRunning" / Error in this line End Sub "JLatham" wrote: And it can do that. Have you checked out the link that Dave Peterson provided below? One way to handle the closing of the workbook is to put the same code that is in Sub stopRunning into the workbook's _BeforeClose() event processor, or simply call stopRunning from there, as: Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "stopRunning" End Sub Remember that the Workbook_BeforeClose() code has to be placed into the ThisWorkbook code module. "Eric" wrote: For using Application.OnTime, do I need to close the excel application in order to stop the process? It seems to me when the file is closed without quiting the excel application, it will keep running the macro again. Does anyone have any suggestions? Thank everyone very much for any suggestions Eric "JLatham" wrote: Stolen from a very nearby universe, er, answer to a question, and modified to protect the guilty. Option Explicit Dim nextRunTime Sub startRunning() 'you could put ' Run "MyCode" 'in the Workbook_Open() event ' MyCode End Sub Sub stopRunning() On Error Resume Next Application.OnTime nextRunTime, "MyCode", , False End Sub Sub MyCode() nextSecond = Now + TimeValue("00:15:00") Application.OnTime nextSecond, "MyCode" 'your process to run right in with this stuff 'it has already set itself to run again in 15 minutes End Sub "Eric" wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
When I try following codes, there is error on this line.
Do you have any suggestions on how to fix it? Thanks in advance for any suggestions Eric Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: Chip Pearson's site explains it: http://www.cpearson.com/excel/OnTime.aspx Eric wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
If you look at Chip's site, you'll see this code:
Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _ Schedule:=False End Sub The "on error resume next" line is there in case there isn't a pending ontime macro for that time. Eric wrote: When I try following codes, there is error on this line. Do you have any suggestions on how to fix it? Thanks in advance for any suggestions Eric Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: Chip Pearson's site explains it: http://www.cpearson.com/excel/OnTime.aspx Eric wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric -- Dave Peterson . -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
Do I need to insert both StopTimer and Workbook_BeforeClose into worksheet in
order to make it work? but I find nothing calling StopTimer under any module. Could you please give me more suggestions? Thanks in advance for any suggestions Eric Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _ Schedule:=False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: If you look at Chip's site, you'll see this code: Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _ Schedule:=False End Sub The "on error resume next" line is there in case there isn't a pending ontime macro for that time. Eric wrote: When I try following codes, there is error on this line. Do you have any suggestions on how to fix it? Thanks in advance for any suggestions Eric Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: Chip Pearson's site explains it: http://www.cpearson.com/excel/OnTime.aspx Eric wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric -- Dave Peterson . -- Dave Peterson . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set macro to run every 15 minutes?
You can call the stoptimer routine in your _beforeclose procedu
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call StopTimer End Sub Then you don't need to duplicate the code (and take a chance that you don't fix both when you have to change it). The StopTimer routine is nice to keep separate -- especially when you're testing and want to kill the next run. Eric wrote: Do I need to insert both StopTimer and Workbook_BeforeClose into worksheet in order to make it work? but I find nothing calling StopTimer under any module. Could you please give me more suggestions? Thanks in advance for any suggestions Eric Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _ Schedule:=False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: If you look at Chip's site, you'll see this code: Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedu=cRunWhat, _ Schedule:=False End Sub The "on error resume next" line is there in case there isn't a pending ontime macro for that time. Eric wrote: When I try following codes, there is error on this line. Do you have any suggestions on how to fix it? Thanks in advance for any suggestions Eric Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "MyMacro", , False / Error in this line End Sub "Dave Peterson" wrote: Chip Pearson's site explains it: http://www.cpearson.com/excel/OnTime.aspx Eric wrote: Does anyone have any suggestions on how to set macro to run every 15 minutes? Thanks in advance for any suggestions Eric -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text entered as minutes/seconds to minutes | Excel Worksheet Functions | |||
Run Macro (VBA) every 5 minutes | Excel Programming | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
add column of minutes, show total in hours & minutes | Excel Worksheet Functions | |||
Run Macro every 30 minutes | Excel Programming |