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


"Peter Beach" wrote in message
...
Hi Martin,

It seems a bit wasteful to possibly get 58 secs into the query and then
abandon it. Would it not make more sense to execute the refresh and then
determine when next to run the query.

Something like:

Sub DoQuery()
Dim dWhen as Double
dWhen = Int(Now) + TimeSerial(Hour(Now), Minute(Now) + 1, 0)
' Start the query
If Now < dWhen Then
Application.OnTime dWhen, "DoQuery"
Else
' Don't know what you want to do if the refresh took longer than 1
min,
either
' run it again immediately, or reschedule it (which seems more
sensible).
If so
dWhen = Int(Now) + TimeSerial(Hour(Now), Minute(Now) + 1, 0)
Application.OnTime dWhen, "DoQuery"
End If
End Sub

Perhaps though you have some reason for specifying the problem the way you
have.

If you did want to do it with your design, surely what you would do is
have
the OnTrack sub set an OnTime call to call StopQuery after 58 seconds.
StopQuery checks to see whether the query is still running and stops it if
it is. The sub then sets an OnTime call to QuerySheets in 2 seconds time.

HTH

Peter Beach

"Martin Wheeler" wrote in message
...
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