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

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

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

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

Just some added information - maybe, as part of your solution, you want to
set a shorter timeout interval:

http://support.microsoft.com/default...b;EN-US;181050
PRB: Internet Explorer Error "Connection Timed Out" When Server Does Not
Respond Within Five Minutes

--
Regards,
Tom Ogilvy


Peter Beach wrote in message
...
Hi Martin,

Maybe I'm not understanding the issue here. All I'm suggesting is that
the
routine processing the query should call itself via Application.OnTime
once
it has finished the query rather than (as you seem to be doing) before it
starts the query. i.e. rather than:

Sub DoQuery()
Application.OnTime Now + TimeValue("00:01:00"), "DoQuery"
MakeQuery
End Sub

change it to:

Sub DoQuery()
MakeQuery
Application.OnTime Now + TimeValue("00:01:00"), "DoQuery"
' Maybe do some more calcs to determine when the appropriate time to run
itself again is.
End Sub

The initial load of the spreadsheet would, of couse, simply call DoQuery.

Regards,

Peter Beach

"Martin Wheeler" wrote in message
...
Hi Peter,
Thanks for the reply.
The problem with your suggestion is that OnTime starts when the app
opens
and runs continously. Every minute on the minute it scans through all
10
wksheets and sees if they need refreshing. Most of the time it is fine,
even if there are 4 or so queries to be done, it works fine. (I have not
run
into the problem you mentioned about triggering a query before the first
is
finished.) But in July/Aug the servers I access slow down( I think for
servicing) and I have this problem.
Ta,
Martin

"Peter Beach" wrote in message
...
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

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