+ Reply to Thread
Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

    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!

  2. #2
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Moscow
    MS-Off Ver
    Excel 2010
    Posts
    291

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

    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

  3. #3
    Registered User
    Join Date
    11-05-2012
    Location
    London Englad
    MS-Off Ver
    Many
    Posts
    22

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1