Code help

[TiTuS]

hi, ive got this bit of code and ive been told that it will make my web querys come up with a runtime error if no data is reciveved. the thing is i dont know how to set it up right in my sheet this is what ive got -

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

i need that to run then an error number trap code... and on error to reapeat the above code...
also if possible can it be set to automatically run the whole code every 5 seconds? thats optional the oter stuff is more important

 

[TiTuS]

bump

 

[parry]

Hi Titus isnt this the same issue as you had before? From what I recall the problem was the messaage appears but is not classed as an error therefore trying to use error handling to resolve this is futile. Unfortunately the message is not turned off by Application.EnableEvents = False either.

I still have no answer for you as basically the QueryTables object has a few design flaws so unless MS re-write it then it may not be able to be resolved.

I looked into the Query_AfterRefresh and QueryTable_BeforeRefresh events and managed to get these to work but the message appears after the BeforeRefresh event and before the AfterRefresh event (ie in between them), so I cant suppress it using these events. Its unfortunate because the AfterRefresh event has a success boolean argument which indicates whether the refresh was successful or not but because the message appears before it can run its a waste of time.

Question: If you add a new query to the site and theres no data, do you get the same message or an error message? If its an error message it can be manageable, as you would delete the query then re-create it.

 

[TiTuS]

ive been told that by adding the line of code i put above to my sheet it will change the message im getting from a message to an error 1004 or something... which yes would mean it could be trapped and maintaned. and sorry i dont follow the question

If you add a new query to the site and theres no data, do you get the same message or an error message?

if the data im after is not there then theres no place for me to capture the web query... cause as the site updates information it creates the table.
(and yes it is along the same lines as what i was looking into before, just a new way of looking at it... if this works what i was told and it is possible to change the message by adding the above code it could be the end of many peoples long searching.
(is there anyway to tell microsoft of this error (bug)

 

[parry]

OK, so this is something new then. So if theres no data then amending the backgroundquery argument causes an error?

See if this returns these messages and not the warning message you got before.

Public Sub Test()
Dim Qrytbl As QueryTable

For Each Qrytbl In ActiveSheet.QueryTables
    On Error Resume Next
    Qrytbl.Refresh False
    
    If Err.Number <> 0 Then
        MsgBox "An error occured so refresh never happened"
    Else
        MsgBox "Refresh was OK"
    End If
    
    On Error GoTo 0
Next Qrytbl

 

[TiTuS]

Yes that worked! it came up and said the message and after pressing ok it was fine!!! oh boy this is getting better lol im getting excited lol... so now just to error trap that message? and on error re refresh?
Man thankyou so so much you are amazing!

 

[parry]

I cant take the credit. Whoever advised you of the background refresh has come across an undocumented way to resolve it. I believe you just need to remove the messaging as it should have refreshed if the message 'Refresh was OK' appeared. You could just use a recursive macro that ran an OnTime event calling this macro to repeat it for a refresh rather than using the built in refresh timer with the querytables.

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

 

[parry]

Heres what I mean by recursive (calling itself). You should also check if the query is already refreshing.

Place this in the ThisWorkbook module

Private Sub Workbook_Open()
Call RefreshQuery
End Sub

Place this in a standard module. eg Module1

Public Sub RefreshQuery()
Dim Qrytbl As QueryTable, Sh As Worksheet

'Change this to the name of your sheet that holds the web queries
Set Sh = Sheets("Sheet1")

For Each Qrytbl In Sh.QueryTables
'If the query isnt already refreshing then refresh it
    If Not Qrytbl.Refreshing Then
        On Error Resume Next
        Qrytbl.Refresh False
        On Error GoTo 0
    End If
Next Qrytbl

'Repeat this procedure every 30 seconds
Application.OnTime Now + TimeValue("00:00:30"), "Refresh Query"

End Sub

 

[TiTuS]

hi parry, man thankyou so so much this is so over whelming..
With that last code posted... its fine on the first refresh then after the 30 seconds it comes up with a message saying the RefreshQuery macro not found....

 

[parry]

My fault, there shouldnt be a space between RefreshQuery so change this line...

Application.OnTime Now + TimeValue("00:00:30"), "Refresh Query"

To...
Application.OnTime Now + TimeValue("00:00:30"), "RefreshQuery"

Some other things you may want to think about...
1) Managing Refresh Times
If you have other sheets on the workbook you may want to only refreshing when the sheet containing the queries is active. That way it cuts down on unnecessary work for Excel if your working with other sheets. I can show you how you would do this if you want.

2) Stopping the macro
You may occasionally want to stop the refresh process. You can stop the macro by CTRL+BREAK but this is not very professional. Instead you may wish to have a method that toggles the refresh processing on and off. This can be done by monitoring a key (say F12) or by having a button on the sheet. Again, if you want to do this let me know and Ill show you how you go about it.