Web Query Query

[TBrewerton]

Hi,
for awhile ive been trying to make a script to make my spreedy self
sufficient so i can walk away and come back and know the exact time
data changed on a site.
For this ive got a query that updates every 3 seconds... the problem is
quite often it gives the error recieved no data (this error can be
recreated by making a site and linking to data on it then deleting the
data on the web... then you should get the message i get)
The problem is nothing will happen on the spreedy until that error is
dealt to.
What i am wondering is there any more stable ways for recieving data
off of a web site, it seems as if its timing out when refreshing or
something and taking it as if its recieved nothing.
So

What other kind of ways are there of extracting data from a web page.

In advance Thankyou so much for your time and effort,

Timothy

p.s. it dosnt seem to have an error number so cant be classed as an
error to use error trapping code... its more a statement or warning
window

 

[Tim Williams]

What is a "spreedy"?

How are you firing the refresh? Perhaps you could show the relevant
code....

You could try

Application.displayalerts=false

before running the query.

Tim.

 

[TBrewerton]

spreedy = spreedsheet
Ok basically its for a tycooning game... one stage of the game consists
of loading your product into a warehouse then depending on how many you
are loading it will take longer or shorter... my code is to constantly
refresh that page so when my product status changes from "loading" to
"loaded" it fires off a macro which records the time at which the job
was loaded... this will help me to find the times of how long each
ammount of jobs takes to load.
at the current time i do not have a refresh method i just do it
manually but if i can stop this message i will have a code to make it
refresh every 3 seconds.
application displayalerts = false before workbook refresh all does not
seem to stop the message.
anyone got any other ideas?

 

[TBrewerton]

any other ideas out there?

 

[TBrewerton]

any other ideas out there, if you want i can e-mail you a copy of a
sheet with the web query on it, and the ideal would be to try get it so
when the error pops up it kills it then refreshes and loops until data
is retrieved.

(e-mail address removed)

 

[TBrewerton]

spreedy = spreedsheet
Ok basically its for a tycooning game... one stage of the game consists

of loading your product into a warehouse then depending on how many you

are loading it will take longer or shorter... my code is to constantly
refresh that page so when my product status changes from "loading" to
"loaded" it fires off a macro which records the time at which the job
was loaded... this will help me to find the times of how long each
ammount of jobs takes to load.
at the current time i do not have a refresh method i just do it
manually but if i can stop this message i will have a code to make it
refresh every 3 seconds.
application displayalerts = false before workbook refresh all does not
seem to stop the message.
any other ideas out there? if you want i can e-mail you a copy of a
sheet with the web query on it, and the ideal would be to try get it so

when the error pops up it kills it then refreshes and loops until data
is retrieved.

 

[Dick Kusleika]

TB

If you refresh in the foreground, you will get a trappable error. Use
syntax like

Sheet1.QueryTables(1).Refresh Background:=False

If there is no data, you will get Run Time Error 1004. No put in the
appropriate On Error statment and you should be able to continue processing
when the web page returns no data. Note that refreshing the foreground will
dramatically slow the time it takes for the code to run, but not necessarily
the time it takes for the whole operation to complete.

 

[TBrewerton]

Hi thankyou so much for your reply if what you say works that will end
months of getting no where!
im very sorry but i am kinda new to excel so is it possible for someone
to show me how to construct that on to a sheet? so the code
Sheet1.QueryTables(1).Refresh Background:=False then an on error
statement to trap it then a on error statement to repeat the above
steps if an error shows. (is it possible for you to put each bit in
steps so i can study it and learn it for myself? thankyou very very much

 

[Dick Kusleika]

TB

Email what you have to me and I'll walk you through the steps.

 

[TBrewerton]

Sorry i dont know if what i think was an e-mail went through to you if
not here is someof what i said if so ive just finished a bit more in my
code as follows.

Sub LoopRefresh()
StartHere:
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

With ThisWorkbook.Sheets("Sheet1").Cells
..QueryTable.Refresh BackgroundQuery:=False
End With

GoTo StartHere
End Sub


This makes the refresh time to be every 10 seconds... now all i need is
how to modiffy that to work with what you were talkiong about... so
instead of showwing the message no data returned... it comes up with a
error and then error traps it and restarts the refresh.

 

[TBrewerton]

Woah im getting close ive made it now so instead of the message poping
up it does a custom message box!!! which means its now possible to
close that box! just working on the coding now!!!

 

[TBrewerton]

The soloution to Web query returned no data message -

Public Sub Test()
Dim Qrytbl As QueryTable

For Each Qrytbl In ActiveSheet.QueryTables
On Error Resume Next
Qrytbl.Refresh False
On Error GoTo 0
Next Qrytbl
End Sub

 

[Don Guillett]

try this
For i = 1 To ActiveSheet.QueryTables.Count
ActiveSheet.QueryTables(i).Refresh False
Next