Web Query Query

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

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@gmail.com> wrote in message
news:1107723934.923836.99380@c13g2000cwb.googlegroups.com...
> 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
>

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?

any other ideas out there?

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.

TBrewerton@gmail.com

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.

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

TBrewerton@gmail.com wrote:
> 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.
>
> TBrewerton@gmail.com

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

TB

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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

TBrewerton@gmail.com wrote:
> 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

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.

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! :D just working on the coding now!!!

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

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

--
Don Guillett
SalesAid Software
donaldb@281.com
<TBrewerton@gmail.com> wrote in message
news:1107975668.282683.7650@z14g2000cwz.googlegroups.com...
> 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
>

I’m using also refresh query to get the data from web. However every now and then when something goes wrong and I get the 1004 refresh query doesn’t work anymore even not from other workbooks. Only think, which helps is to close excel and start it again. My Excel is 2000 and I run it on win2k.
Has anyone experienced the same problem or know how to handle 1004 error correctly?

Thanks in advance,
Michal