Application.OnTime rampage?

[JazzSP8]

Hi Guys

I've created a spreadsheet which automatically imports files, runs a couple of tried and tested macros every hour and then Emails (via Outlook) the results out to about 30 people.

I use the Application.OnTime method;

Sub ActivateOnTime()
Application.OnTime TimeValue(Hour(Now) + 1 & ":00:00"), "Sheet2.AutoUpdaterDay"
End Sub

This launches this bit of code;

Sub AutoUpdaterDay()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Generating Auto Update"
        Module1.ImportData
        Sheet4.DeleteOtherRows
        Sheet4.Tidy
        Sheet3.DeleteSkillsetRows
        Sheet3.TidyUp
    Sheet2.Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.StatusBar = " "
    
    Sheet2.Email
    
    Module1.ActivateOnTime

End Sub

I trialed this for about a day and it worked perfectly, then yesterday I had it running "live", it seemed to be working ok, but after running for 6 hours (meaning it had run 6 times) it went on a rampage and sent its 7th Email seven times causing me a flood of phone calls and a royal pain. I couldn't find the soloution, or cause it to replicate yesterday and all seems fine again this morning, the only thing different between the trial run and today is that I have the Emails on manual sending

This is the code I used to send the Email (found on this board).

Sub Email()
  
Dim appOutlook As Object
Dim MailItem As Object
Dim FSObject As Object
Dim myTo As String
Dim myCC As String
Dim mySub As String

Set rngeSend = Application.Range("A1:D15")
Set FSObject = CreateObject("Scripting.FilesystemObject")
Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)

tmpFile = FSObject.GetSpecialFolder(2)
tmpFile = tmpFile & "\myRange.htm"

ActiveWorkbook.PublishObjects.Add(xlSourceRange, tmpFile, rngeSend.Parent.Name, _
    rngeSend.Address, xlHtmlStatic).Publish True
    
Set TStream = FSObject.OpenTextFile(tmpFile, 1)
strHTMLBody = TStream.ReadAll
strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)

TStream.Close
Kill tmpFile

With MailItem
    .HTMLBody = strHTMLBody
    .To = Application.Range("A30") & "; " & Application.Range("A31") 
    .Subject = Application.Range("B1")
    .Display
    'Application.SendKeys "%s", True
End With

Set MailItem = Nothing
Set appOutlook = Nothing
Set FSObject = Nothing
Set TStream = Nothing

End Sub

Yesterday when "live" the Application.Sendkeys line wasn't commented out, but thats the only difference.

The spreadsheet is running on Windows 2000 using Office 2000 and pretty much left alone apart from a couple of scedhuled reports, none of which run on the hour.

Can anyone see why it would suddenly go hyperactive on me, or give me something else to look at or think about?

 

[Andrew Poulsom]

Are you cancelling the OnTime event when you close the workbook?

Here is an example:

http://www.mrexcel.com/board2/viewtopic.php?t=237053

 

[JazzSP8]

Hi Andrew

Thanks for the reply.

I wasn't aware it was neccessary to stop this kind of timing, I have the workbook close itself at five minutes past seven and thought it would automatically stop any timing when Excel closed, resetting itself when it opened.

Is this the code that I would need to incorporate?

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
On Error Resume Next 
Application.OnTime EarliestTime:=vSetTime, Procedure:="Recalc", Schedule:=False 
On Error GoTo 0 
End Sub

BTW - The workbook wasn't closed during the day, I simply copied the file from my computer onto the other, opened it and left it alone for the duration, well, until it all went wrong

 

[Andrew Poulsom]

Is this the code that I would need to incorporate?

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
On Error Resume Next 
Application.OnTime EarliestTime:=vSetTime, Procedure:="Recalc", Schedule:=False 
On Error GoTo 0 
End Sub

Yes, but remember to use vSetTime when you schedule the procedure with the OnTime method. In the cancelling call EarliestTime must be the same as in the currently scheduled call.

 

[JazzSP8]

With you - Shall add the code in now, do some more testing and see what happens - Thanks for your help

Can I just ask though, did I get the problem because I had started a timed procedure in the Worksheet and never stoped it?

 

[Andrew Poulsom]

Can I just ask though, did I get the problem because I had started a timed procedure in the Worksheet and never stoped it?

I'm not sure what caused your problem, but failing to cancel an OnTime event can cause problems.