How to set macro to run every 15 minutes?
in [Programming]
|
From: JLatham on 20 Apr 2010 08:15 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
From: Eric on 20 Apr 2010 17:15 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
From: Eric on 20 Apr 2010 17:18 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 > . >
From: Dave Peterson on 20 Apr 2010 18:40 If you look at Chip's site, you'll see this code: Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedure:=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
From: Eric on 20 Apr 2010 22:57 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,Procedure:=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,Procedure:=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 > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Adding code Next: 2007 Customui - is it possible to control the visbility of a "grou |