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, procedu=RunWhat, _
schedule:=True
SetFlag = True
.ScreenUpdating = oldAppScreenUpdate
End With
End If
End Sub