Various errors with VBA Loop to pull HTML data

Question

I've been getting various errors with the below VBA code (most recent error is Run-time error '70': permission denied). Basically the code/worksheet connects to an intranet IE database of customers, searches customer activity and imports any activity to the worksheet (will eventually use the activity for reporting). Here's where I run into the errors, depending on the length of time I'm searching I sometimes have multiple pages of activity to pull which requires clicking the "next" button and pull the data from each page until there is no longer a "next" button (no more activity). The loop I have set up will pull from the first page, click the "next" button then sometimes pull from the second sheet but then it trips the error. So I think the error has something to do with the loading of the pages but I've added pauses to allow for loading but still run into the same errors. I'm really stuck on this and unfortunately I can't move forward with the project until I can solve this issue.

Here is the code snippet:

    Dim TDelements As IHTMLElementCollection
    Dim TDelement As HTMLTableCell
    Dim r As Long, i As Long
    Dim e As Object

    Set TDelements = IE.document.getElementsByTagName("tr")
    r = 0
    For i = 1 To 1
        Application.Wait Now + TimeValue("00:00:03")
        For Each TDelement In TDelements
            If TDelement.className = "searchActivityResultsContent" Then
                Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
                r = r + 1
            ElseIf TDelement.className = "searchActivityResultsContent" Then
                Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
                r = r + 1
            End If
        Next
        Application.Wait Now + TimeValue("00:00:02")
        Set elems = IE.document.getElementsByTagName("input")
        For Each e In elems
            If e.Value = "Next Results" Then
                e.Click
                i = 0
                Exit For
            End If
        Next e
    Next i
    Do Until Not IE.Busy And IE.readyState = 4
        DoEvents
    Loop
    IE.Quit
End Sub

Any help/suggestions would be very much appreciated. Thank you!

Answer 1

Looking at your code:

'getting any TD elements here
Set TDelements = IE.document.getElementsByTagName("tr")

'waiting here....
Application.Wait Now + TimeValue("00:00:03")

'now trying to use items in TDelements...
For Each TDelement In TDelements
   '...
Next

Are you waiting for the page to load when you use Application.Wait ?

If Yes then you should know that TDelements isn't dynamic - it won't update itself as new TD elements are loaded: it's just a snapshot of the elements which were present when you called getElementsByTagName("tr"). So call that after the wait.