Background Query Timeout
 

I am posting this because I just searched the archives and found dozens of
unanswered questions about how to control the web query timeout. I finally
got lucky with VBA Help -- which is very frustrating to use. It seems there
are two parameters:

..RefreshPeriod and .ResetTimer

The useful one is the 1st. There is also mention of QueryTimeout and
Connection Timeout elsewhere in VBA help, but they give errors inside a web
query.

Here's my working code that seems to timeout after 10 seconds and continues
on instead of freezing up when your website does not respond (hard to test
since I can't force a website to not respond!). You'll have to invent your
own method of deciding whether or not the query was successful. I test some
cell I know should have data. If it doesn't, I assume it timed out and I try
again or you can abort with an error message.

With ActiveWorkbook.Worksheets("Temp").QueryTables.Add( _
Connection:="URL;" & strurl,Destination:=Worksheets("Temp").Cells(1, 1))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.SaveData = True
.RefreshPeriod = 10 'Gives up after 10 seconds of no response
.BackgroundQuery = False 'WAIT for results
.Refresh
End With

I did see Query Events to give a True/False condition upon successful query,
but they require some serious setup that is beyond me, especially since I'm
using a temporary sheet created on the fly just to get the web data and then
it gets deleted.

Good Luck,

Terry

Background Query Timeout
 

Please disregard the advice below. It is wrong.

RefreshPeriod is for automatic refreshes of a query and the time value is in
minutes, not seconds.

Still on a quest to find out how to set a time-out for a web query!

Terry

On 6/15/04 4:50 PM, in article , "Terry"
wrote:

I am posting this because I just searched the archives and found dozens of
unanswered questions about how to control the web query timeout. I finally
got lucky with VBA Help -- which is very frustrating to use. It seems there
are two parameters:

.RefreshPeriod and .ResetTimer

The useful one is the 1st. There is also mention of QueryTimeout and
Connection Timeout elsewhere in VBA help, but they give errors inside a web
query.

Here's my working code that seems to timeout after 10 seconds and continues
on instead of freezing up when your website does not respond (hard to test
since I can't force a website to not respond!). You'll have to invent your
own method of deciding whether or not the query was successful. I test some
cell I know should have data. If it doesn't, I assume it timed out and I try
again or you can abort with an error message.

With ActiveWorkbook.Worksheets("Temp").QueryTables.Add( _
Connection:="URL;" & strurl,Destination:=Worksheets("Temp").Cells(1, 1))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.SaveData = True
.RefreshPeriod = 10 'Gives up after 10 seconds of no response
.BackgroundQuery = False 'WAIT for results
.Refresh
End With

I did see Query Events to give a True/False condition upon successful query,
but they require some serious setup that is beyond me, especially since I'm
using a temporary sheet created on the fly just to get the web data and then
it gets deleted.

Good Luck,

Terry

Background Query Timeout
 

Terry wrote ...

Still on a quest to find out how to set a time-out for a web query!

Perhaps this the same issue as the one Robin Hammond posted on recently:

http://groups.google.com/groups?thre...tvigato r.com

Jamie.

--