On Time

[Guest]

I am using On Time routine from Chip Pearsons site as Follows:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRUnWhat = "Macro2"

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


Sub Macro2()

My code

StartTimer

End Sub


Where do I sore the code and how do I actually start the procedure/
Thanks!

 

[Tom Ogilvy]

In the vbe with our workbook as the active project,
Insert=>Module

Place it there

To start it, in the project explorer in the vbe, select the ThisWorkbook
entry. In the dropdowns at the top, select workbook on the left and Open on
the right.
Private Sub Workbook_Open
StartTimer
' or if you want to run you code on opening
'Macro2
End Sub

 

[Bob Phillips]

You don't start the timer in the ontime procedure, you start it outside. The
OnTime will run that procedure after the specified delay.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRUnWhat = "Macro2"

Sub Invokee()
StartTimer
End Sub

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


Sub Macro2()
My code
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Tom
Thanks for the reply
When I open the workbook I get
Compile Error:
Ambiguous name detected: StartTimer

 

[Tom Ogilvy]

Do you have two procedures named StartTimer? That is the usual cause of this
error. Also, you should not name a module the same as a procedure, although
would be more unlikely to be the case.

 

[Guest]

OK I did find a duplicate StartTimer()
I have deleted all code except module1:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRUnWhat = "Macro2"

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


Sub Macro2()
'
'
Application.DisplayAlerts = False
applications.ScreenUpdating = False

Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Mypath\myfile.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Applications.ScreenUpdating = True

StartTimer

End Sub

and on ThisWorkbook

Private Sub Workbook_Open()
StartTimer
Macro2
End Sub

Macro2 runs on open but the 30 second update is not working

Thanks

 

[Tom Ogilvy]

Well, by calling macro2, which calls starttimer and calling starttimer
directly, you should have two runs scheduled rather than none at all.

What I was suggesting was to call one or the other, not both:

Private Sub Workbook_Open()
Macro2
End Sub

Macro2 executes a starttimer, so it should continue the chain.

 

[Guest]

Latest Recap if I understand correctly...

In this workbook
Private Sub Workbook_Open()
StartTimer
End Sub

Module1

Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRunWhat = "Macro2"

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


Sub Macro2()
Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\mypath\myfile.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close

End Sub


And it still doesnt run.
Im sorry Im just not getting it. I aprreciate all of your help.

 

[Tom Ogilvy]

You had some typos in your commands. This worked for me:

In module1
Public RunWhen As Double
Public Const cRunIntervalSeconds = 30
Public Const cRunWhat = "Macro2"

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


Sub Stop_Timer()
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat,
schedule:=False

End Sub


Sub Macro2()
On Error Resume Next
Kill "C:\Mypath\myfile.xls"
On Error GoTo 0
Workbooks.OpenText Filename:="C:\Mypath\myfile.txt", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\mypath\myfile.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
' added line to schedule the next run
StartTimer
End Sub
--------------------
In the Thisworkbook Module

Private Sub Workbook_Open()
StartTimer
End Sub

--
Regards,
Tom Ogilvy



--------