VBA的各种错误循环来拉取HTML数据 [英] Various errors with VBA Loop to pull HTML data

问题描述

我已经收到以下VBA代码的各种错误（最近的错误是运行时错误'70'：权限被拒绝）。基本上，代码/工作表连接到客户的Intranet IE数据库，搜索客户活动并将任何活动导入工作表（最终将使用活动进行报告）。这里是我遇到错误的地方，这取决于我正在搜索的时间长度我有时会有多个活动页面需要点击下一步按钮，并从每个页面拉取数据，直到不再有下一个 按钮（没有更多的活动）。我设置的循环将从第一页拉出，点击下一步按钮，然后有时从第二张表中拉出，但是会跳过错误。所以我认为错误与页面的加载有关，但我添加了暂停以允许加载，但仍然遇到相同的错误。我真的坚持这个，不幸的是我无法向前推进项目，直到我能解决这个问题。

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.

这是代码片段：

    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!

推荐答案

查看您的代码：

'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

您在使用应用程序时等待页面加载。等待？

如果是，那么你应该知道 TDelements 不是动态 - 它不会自动更新，因为新的TD元素被加载：它只是当您调用 getElementsByTagName（tr）。所以请等待后面的。

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.

这篇关于VBA的各种错误循环来拉取HTML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！