OnTime running 2 subs

  • Thread starter Thread starter Martin Wheeler
  • Start date Start date
M

Martin Wheeler

xl2000
My app uses OnTime to call web queries. Every minute, on the minute, is
scans all the worksheets to see if any need to be updated and does so if
required.
The problem is that if the server I am accessing is slow and the query is
not finished within the minute it collides with the next minute and the only
way out is to crash out of xl and I lose all unsaved data, etc.
Below is the code I use to start OnTime. "QuerySheets" calls the web query
and does all the formatting stuff. What I want is to have another sub that
cancels any running queries at 58 secs past the minute. It will do this
every minute to make sure no queries are running when the next minute
starts. So I think that RunWhat should call a new sub, "StartWork" that
calls "QuerySheets" and "StopQuery". StopQuery will do just that. Problem
is I do not know how to write the code.
Below is what I have so far. Any help would be greatly appreciated.
Ta,
Martin

Public Sub StopQuery()
Dim ST As Date
Dim ST1 As Long
Dim T As Date
ST = TimeSerial(Hour(Time), Minute(Time) + 0, 58)
ST1 = Hour(T - ST) * 60 + Minute(T - ST)
Cancel Refresh
End Sub

Public Sub OnTrack() 'sets time and does the OnTime thing
Dim oldAppScreenUpdate As Boolean
Dim RunWhen As Date
Dim RunWhat As String
On Error Resume Next
If Not SetFlag Then 'flag is to prevent repetitions
With Application
oldAppScreenUpdate = .ScreenUpdating
.ScreenUpdating = False
RunWhen = TimeSerial(Hour(Time), Minute(Time) + 1, 0)
RunWhat = "QuerySheets"
.OnTime earliesttime:=RunWhen, procedure:=RunWhat, _
schedule:=True
SetFlag = True
.ScreenUpdating = oldAppScreenUpdate
End With
End If
End Sub
 
Hi Peter,
Thanks for the reply.
What seems to happen is that once it has gone past the minute I rarely if
ever get the refresh. Instead it just keeps saying "connecting..."
So I have found from hard experience that I am better of ctrl, alt, del my
way out of xl as that is the only way I have found to keep moving. Hence my
idea of coming up with a way of canceling the refresh. In the course of a
day I do about 80 refreshes. Today that will be over a 2hr period.
Ta,
Martin
 
Hi Martin,

I'm guessing that if the timer triggers a second refresh before the first is
finished you will get into all kind of bother. That was why I suggested not
triggering another refresh on the timer until the first refresh has
finished - as that would seem to eliminate the risk of XL getting confused.

In theory your second refresh (on the timer) shouldn't trigger as XL should
be aware that it is busy doing something else (in this case your first
query). Unfortunately a number of Windows processes don't "block" quite as
well as they should and I would guess that XL is re-executing the query
before the first one has finished, and that is almost certain to cause all
kinds of problems for which alt-ctrl-del is likely to be the only solution.

I would suggest you try not setting the timer event until *after* the query
has executed successfully and see whether that makes the system more stable.

Good luck, and post back if you have any further queries.

Regards,

Peter Beach
 
Back
Top