Querytable/Web Queries problem - how to reset count and ignore errors

Hi all!!

this is the code i use:

Sub WebQueries()
Dim Wq As Range
Dim Wqs As Worksheet
Dim i, j As Integer

Set Wq = ThisWorkbook.Sheets(3).Range("A1")
Set Wqs = ThisWorkbook.Sheets(3)

On Error Resume Next
For i = 0 To 13
    Wqs.Range(Cells(3 + 20 * i, 9), Cells(1 + 20 * (i + 1), 20)).Clear
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.nbrb.by/statistics/PrStavkiN/?date=" & Wq(2 + 20 * i, 9), Destination:=Cells(4 + 20 * i, 9))
        .Name = "?date=" & Left(Wq(2 + 20 * i, 9), 5)
        .WebTables = "15"
        .Refresh
    End With
Next i
On Error GoTo 0

Set Wq = Nothing
Set Wqs = Nothing

End Sub

as the result i get 14 tables from web when what table it will be depends on dates in cells, say, Wq(2 + 20 * i, 9)

The problem is that when excel start count number of requests by this data table - i get no data. it looks like follow:?date=15.10_11, where 15.10 is the table name and 11 is number of requests. This think wont work but if i put only ?date=15.10 in cell and refresh then it works great. So 2 questions.

1 - how to prevent excel from counting and do query always like 1st time
2. My on error resume next doesn't work if i try request data that doesn't exist and i get nasty messages. How i really tell exxcel to ignore these errors??

Thanks in advance!

Well, to prevent from counting it's must truly delete the QueryTable. the best way to go:

For Each qt In Thisworkbook.Sheets(3).QueryTables
    qt.Delete
Next qt

But how to ignore errors is still question... Turning off alerts doesn't help as well

What do you mean by "ignoring errors". If you truly ignored them, there would be no need for this query?

I am guessing that you are after something other than "On Error Resume Next".

You can use following function that you can run in Debug mode. In first run, pass deleteVal as false and press Ctrl-G during Debug mode to see names of tables.

Sub DeleteAllWebConnectors(filter As String, deleteVal As Boolean) 'DeleteAllConnectionsQueryTablesAndNames
    Dim i As Integer
    Dim QT As QueryTable
    Dim Cnxn As Variant
    'Dim CnxnName As Name

    For Each QT In ActiveSheet.QueryTables
        Debug.Print "Query Table named", QT.Name ', QT.WorkbookConnection.Name
        If ((Len(filter) = 0) Or (Len(filter) <> 0) And (InStr(1, QT.Name, filter) > 0)) Then
            Set Cnxn = QT.WorkbookConnection
            If Not Cnxn Is Nothing Then
                'On Error Resume Next
                If Cnxn.Ranges.Count > 0 Then
                    Debug.Print "Linked Connection ", Cnxn.Name, Cnxn.Ranges(1).Address
                Else
                    Debug.Print "Linked Connection with no Linked Cell Address", Cnxn.Name
                End If
                If deleteVal Then Cnxn.delete
            End If
            If deleteVal Then
                QT.delete
            End If
        End If
    Next
    
    For Each Cnxn In ActiveWorkbook.Connections
        Debug.Print "Connection Named ", Cnxn.Name ', Cnxn.Ranges(1).Address
        If ((Len(filter) = 0) Or (Len(filter) <> 0) And (InStr(1, Cnxn.Name, filter) > 0)) Then
            If deleteVal Then Cnxn.delete
        End If
    Next
End Sub