Does anyone have any suggestions on how to set macro to run every 15 minutes?
Thanks in advance for any suggestions
Eric

See:
http://www.ozgrid.com/Excel/run-macro-on-time.htm



-- 
Regards
Dave Hawley
www.ozgrid.com
"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:A6A6F8B2-6FB6-4E02-87F9-4F8D08626223@microsoft.com...
> Does anyone have any suggestions on how to set macro to run every 15 
> minutes?
> Thanks in advance for any suggestions
> Eric 

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

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

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

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

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

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
> .
> 

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

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
> .
> 

You can call the stoptimer routine in your _beforeclose procedure:

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,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
> > .
> >

-- 

Dave Peterson