0

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!

omegastripes
  • 12,351
  • 4
  • 45
  • 96
Dick Plixen
  • 61
  • 1
  • 7
  • Which line is giving the error? Use [IE, document and element state checks sequentially](http://stackoverflow.com/a/32170074/2165759) instead of `Application.Wait`. Or you may try [to retrieve the data via XHR](http://stackoverflow.com/a/35700277/2165759). – omegastripes Mar 03 '16 at 20:23
  • Thanks for the response, the line giving the error (most of the time anyway) is "For Each TDelement in TDelements". As for your suggestions, I have tried using the IE, document and element state checks and I still run into the same problem... I switched it to the application.wait command because it sometimes takes a few seconds to load the activity pages. I apologize I'm not by any means an expert at VBA, what it XHR? And how could I utilize it in this sense? – Dick Plixen Mar 03 '16 at 20:52
  • Could you share the URL you are working with? Regarding XHR, try to follow the steps by the link in my previous comment. – omegastripes Mar 03 '16 at 21:29
  • Sorry, it's an intranet web based program so it's only available on my network. I see what you're saying regarding XHR... I'm not completely sure if I'll be able to get access to the XML files but that would definitely be an easier solution so I'll give it a try! – Dick Plixen Mar 04 '16 at 16:54
  • `Dim` all variables as `Variant`, after the error occurs, go to debugger, type in Immediate window `? TypeName(TDelements)`, `? TDelements.length` and `? TypeName(TDelement)`. What output do you have? – omegastripes Mar 04 '16 at 18:59
  • Check my answer [here](http://stackoverflow.com/a/35926095/2165759) – omegastripes Mar 10 '16 at 20:12

1 Answers1

0

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks for the response! I tried what you suggested, calling the `getElementsByTagName("tr")` after the wait and unfortunately I still run into the same "permission denied" error... although I seem to be getting the error at different spots. I managed (only once) to run through the loop twice before throwing the error which is the farthest I been able to get so you may be on the right track here! – Dick Plixen Mar 04 '16 at 16:46
  • The error you're getting is tricky to fix. If you increase the wait time does that help at all? – Tim Williams Mar 04 '16 at 17:25
  • Unfortunately no... that's one of the first things I tried and it still throws the error it just takes longer to get there. I've even tried putting the wait in various spots that I thought made sense but I continue to run into errors. – Dick Plixen Mar 04 '16 at 17:54