Invalid Web Query Error

[Alfredo_CPA]

How can Ignore a "Invalid Web Query Error". I need excel to ignore the error
and keep procesing the next tables and them process again based in the
refresh period
This is my code:
Application.DisplayAlerts = False
On Error Resume Next
With Sheets("1").QueryTables(1)
.RefreshPeriod = 1 '1 minute
End With
On Error Resume Next
With Sheets("2").QueryTables(1)
.RefreshPeriod = 1 '1 minute
End With
On Error Resume Next
With Sheets("3").QueryTables(1)
.RefreshPeriod = 1 '1 minute
End With
On Error Resume Next
With Sheets("4").QueryTables(1)
.RefreshPeriod = 1 '1 minute
End With

etc...

 

[Joel]

Yuo may want to try to open an Access application instead of doing a query


AccessObj = getObject("c:\temp\MyDataBase.mdb")
Set qdfBonusEarners = AccessObj.CreateQueryDef("")
With qdfBonusEarners
.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;" & _
"DSN=Publishers"
.SQL = "SELECT * FROM titleauthor " & _
"WHERE title_id = '" & _
rstTopSeller!title_id & "'"
Set rstBonusRecipients = .OpenRecordset()
End With


or
AccessApp = Createobject("Access.Applicatioon")
AccessApp.Visible = true
AccessObj = AccessApp.OPenDataBase("c:\temp\MyDataBase.mdb",True,True)
Set qdfBonusEarners = AccessObj.CreateQueryDef("")
With qdfBonusEarners
.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;" & _
"DSN=Publishers"
.SQL = "SELECT * FROM titleauthor " & _
"WHERE title_id = '" & _
rstTopSeller!title_id & "'"
Set rstBonusRecipients = .OpenRecordset()
End With

 

[Alfredo_CPA]

I'm sorry, but I have very limited knlodge about VBA.
Is it possible to do it just in excel?? If not, how can add your code to my
code/spreadsheet? (my code is inside an excel VBA module and I have a
worksheet for each web query)

 

[Don Guillett]

try this. UN tested. But maybe all could be on ONE query with variables
instead of 4 queries.

sub setrefreshonfoursheets
On Error Resume Next
for i=1 to 4
Sheets(cstr(i)).QueryTables(1).RefreshPeriod = 1 '1 minute
next i
end sub

 

[Alfredo_CPA]

Thanks Don, the code is ok to make it smaller.
But the "invalid web query Error" keeps bugging me.
The way I'm trying to skip the error is this but it doesn't work:

Sub setrefreshon13sheets()
Application.DisplayAlerts = False
On Error Resume Next
For i = 1 To 13
Sheets(CStr(i)).QueryTables(1).RefreshPeriod = 1 '1 minute
Next i
End Sub

 

[Don Guillett]

Send a copy of your workbook to my address below along with a snippet of
this msg in an inserted sheet and I will take a look.

 

[Don Guillett]

This should explain it. Best to use one query and change parameters
accordingly.
http://support.microsoft.com/kb/810183