Scheduling Procedures With OnTime

[mowen]

Hi
I try to use the procedure to http://www.cpearson.com/excel/ontime.htm

Here are the code, but I get error message right away. Can some on
help me?

I want to run this every 2 minutes only on this workbook and save as
html files.

Sub StartTimer()

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub


Sub The_Sub()
'
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"D:\Documents and Settings\Rune\My Documents\M
Webs\myweb\public_html\Resultatliste.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False
CreateBackup:=False
'
StartTimer

End Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"D:\Documents and Settings\Rune\My Documents\M
Webs\myweb\public_html\Resultatliste.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False
CreateBackup:=False
End Sub

regards
Run

 

[Dave Peterson]

When you copied Chip's code, you kind of messed up the order of things:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"c:\Resultatliste.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
application.displayalerts = true
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

(don't forget to add that StopTimer code so you can stop it when you want to.)

 

[mowen]

Hi Dave

Thanks for helping me out, but if I want to do so that the macro onl
run in This Workbook. How will the start of the code become then?

I'm not so familiar with vba coding.

Run

 

[Dave Peterson]

oooh. Good point. You may have lost your workbook with the code the way it was
and various active workbooks--you'll want to check to see if you have to restore
from backups!

Thisworkbook.SaveAs Filename:= _
"c:\Resultatliste.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False

Activeworkbook got changed to ThisWorkbook in the The_Sub procedure.

And I'd add two more procedures to your General module of that workbook. The
final version would look something like:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:= _
"c:\Resultatliste.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
application.displayalerts = true
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

sub auto_open
call StartTimer
end sub

sub Auto_close
call StopTimer
end sub

So when you open the workbook, the timer starts. And when you close the
workboo, the timer stops.