Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OnTime running 2 subs
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
Basic Question on Subs | Excel Worksheet Functions | |||
Combining two Subs | Excel Discussion (Misc queries) | |||
Sort subs. by amount | Excel Discussion (Misc queries) | |||
Common Functions and Subs | Excel Discussion (Misc queries) |