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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.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.

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.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()
StartHe
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