Web Query refresh problem

[TBrewerton]

Hi, ive got a spreedsheet in excel that has a multiple number of web querys in it, all doing verious different things, some update hourly others on the minute. The problem im incuring is that about 6 times out of 10 the refreshed data comes up with the error "No data collected" well along those lines anyway...
I want a macro or script that can run through VB(a) that does the following,
On a refresh if it encounters such error as stated above, it will click ok on the error and refresh the item that encounted the error again until it finally recieves the data, then move on to the next query... Either that or just as good would be for it to work for a single query so refreshes/closes the error window until data is recieved.

In Advance thankyou very much!
If you dont quite understand go into Excel and create a web query of anything then right click it and refresh im sure after doing it a couple of times you will encounter the same error.

In Advance Thankyou so much,
Tim

 

[Geneticus]

Hmmm not my area, but...

If you can move the query into a macro, you could pass the error into a variable then check if the error has changed or is Null before moving on to the next query. If you know the error # that is generated by the failure you could change the IF statement to check for that number...

sub MyQuery()
a = 0
On Error Resume Next
'Insert Web Query here'
a = Err.Number
If a >0 then
MyQuery
End If

This should loop through the query until there is no error.

Here is a test sample that generates error 11 and shows you the error # before restarting the sub. I recomend putting some breaks in there before running it so you can look at it step by step and because it will loop forever...

Sub MyQuery()
a = 0
MsgBox "Example error number is " & a & "."
On Error Resume Next
'The next line generates Division by 0 error
b = 6 / 0
a = Err.Number
If a > 0 Then
MsgBox "Example error number is " & a & "."
MyQuery
End If
End Sub

 

[TBrewerton]

Hi, Thanks heaps for the reply and if i could get the first part you posted to work i think that could be what im after.
The problem is though that it dosnt bring up an error number when i get that error...
You would be able to see the error if you open a new book and create a web query of anything and just keep refreshing, im sure youll get it rather soon to.

 

[Geneticus]

Hmmm Not sure If the Cells property will work here but it doesn't give me a compile error....

Sub MyQuery()
a = 0
On Error Resume Next
Cells(2, 1).QueryTable.Refresh BackgroundQuery:=False
a = Err.Number
If a > 0 Then
MyQuery
End If
End Sub

That's the best I can do without the exact error message...

 

[TBrewerton]

Well i cant explain how i get the error better than what i have and it dosnt have a number but i will type the error word for word and describe its apperance.

On the blue bar accross the top it just says Microsoft Excel

In the grey message box to the left is an ! in a yellow trangile and this text

This Web query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar.


Thats word for word caps for caps , for ,

Maybe that could help? i dont know my VB(a) isnt to hot.
But 1 thing i do know is when that error appears i can be on the site its saying theres no data returned from and its deffinitly there so im sure its a problem with excel and not the way im doing things... so if i can work around it ill be extremly happy.

Thankyou for your willingness to help me out! its much apreciated!!!

Tim