Application.OnTime rampage?

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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;

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

This launches this bit of code;

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

Code:
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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?

Code:
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 :oops:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is this the code that I would need to incorporate?

Code:
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

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,136,990
Messages
5,678,989
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top