# VBA; How to abort a querytable refresh?



## balloon_tom (Jan 5, 2006)

My Excel 2003 workbook contains a macro, which when activated, performs several internet queries. An example of one of these queries is included below.
Normally, each query takes less than 1 second (or so) to complete. Occasionally, a query gets 'hung up'. The website may be down, over burdened, or who knows what. The program then waits from 3 to 5 MINUTES waiting for a responce. During that time, I am unable to break, pause, or do anything.
I would like to have some sort of TIMEOUT, such that if a responce is not received within, say 5 seconds, the query will abort.
Can anyone please help?
Thanks
Tom

Sub GetTimeOfDay()
'Code to download the TIME OF DAY from the US NAVAL OBSERVATORY
Dim b As String
b = "url;http://tycho.usno.navy.mil/cgi-bin/timer.pl"
Sheets("Sheet1").Range("A1:A15").ClearContents
With Sheets("SHEET1").QueryTables.Add(Connection:= _
b, Destination:=Sheets("Sheet1").Cells(1, 1))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = False
End With
End Sub


----------



## OBP (Mar 8, 2005)

You could include a "key press" test in the code, if any key is pressed it aborts, but I am not sure that will work if Break doesn't work.
Have you looked at the VB Editor help for "Timer", that sounds like it would fit your requirements.


----------



## balloon_tom (Jan 5, 2006)

I've tried all of the methods listed in help (ithink there were 3). None worked. Any more ideas?
Tom


----------



## OBP (Mar 8, 2005)

Does that include the Form's Timer options?


----------



## MRdNk (Apr 7, 2007)

balloon_tom,

Did you solve this problem? 
I've been looking at, and I can't work it out, if you found the answer, let me know.


----------



## balloon_tom (Jan 5, 2006)

Not solved yet and I'm about at my wit's end...a very short trip for me. I had not seen OBP's reply about the form's timers, but as this is not within a form that may not apply.
I have tried: Application.ODBCTimeout = 2 prior to the query. It did not help. But then again I'm not sure what an ODBC is, or an SQL, or JET or all of these other data bases they talk about in the help section. I just know it's a webquery.
One thing I did notice, the 'freeze' time is 300 seconds (5 min.). While online today I discovered an article about DNS caching. It seemed to be relavant as XP's system defaults to a 300 second delay if the DNS fails. The fix is to add a couple of lines to the registry:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dnscache\Parameters]
MaxNegativeCacheTtl=0 
NetFailureCacheTime=0 
NegativeSOACacheTime=0

The link is: http://www.speedguide.net/read_articles.php?id=158

I really thought I had solved the problem so I added the lines but it did not help.
I do appreciate your persistance.
I'm not a complete dummy (I don't think). This work book has over 100 macros, functions and userforms (most written by me) and I've been able to make them all work...except fot this one.

BTW, this is EXCEL 2003 PRO SP2, XP PRO SP2, running on an Intel E6600.
Thanks for your time,
Tom


----------



## MRdNk (Apr 7, 2007)

I found this info, and was trying to adapt it, but I'm not sure its possible using this method:

http://www.automateexcel.com/index.php/2004/10/14/excel_vba_timer


----------



## balloon_tom (Jan 5, 2006)

Actually, I'm using a timer to tell me how long the query took. Good information, but I think I need a timer that can interupt the query.
Tom


----------



## MRdNk (Apr 7, 2007)

Yeah, that's what you need, and that's something I can't figure out, I would be interested in what the answer is.


----------



## OBP (Mar 8, 2005)

Sorry, Tom, I had my Access hat on when I mentioned Form timer.


----------



## balloon_tom (Jan 5, 2006)

OK, I found where the magic 300 second (5 minute) timeout is coming from. The ftp server I'm talking to disconnects after 300 seconds of idle time. So, I still need to detect about 5 seconds of idle time on MY end and then cancel the query.


----------

