excel - 循环通过超链接集合时，VBA 抛出运行时错误“70 权限被拒绝”

Question

像许多人一样，我使用 Excel 和 VBA 的组合来抓取公共机构网站的数据。立即使用的是在 SEC 的 EDGAR 网页上搜索公司税和罚款数据。我使用 MSXML2 库成功检索了一个网页，在该页面上创建了一个链接集合，并尝试按如下方式遍历该集合：

Option Explicit

Private m_html As MSHTML.HTMLDocument
Private m_wsBuffer As Excel.Worksheet

Private Sub GetData(strSymbol As String)

  On Error GoTo ErrorHandler
  Dim html As MSHTML.HTMLDocument
  Dim elements, documents As IHTMLElementCollection
  Dim e As IHTMLElement
  Dim strUrl As String
  Dim iCol As Integer

  strUrl = "https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=" & strSymbol & _
   "&type=10-k"

  GetHTML strURL

  Set elements = m_html.getElementsByTagName("a")

  For Each e In elements

    Debug.Print "Checking file at " & e.href

    ' Check whether the link is to an archive index page
    ' Not every agency uses this construct; but this is just for test purposes
    If InStr(1, e.href, "index.htm", 1) > 0 And InStr(1, e.href, "archive", 1) > 0 Then

      ' Use the GetHTML subroutine to retrieve the archive link
      GetHTML e.href

      Set documents = m_html.getElementsbyTagName("div")
      iCol = Excel.WorksheetFunction.Match("dateFiling", m_wsBuffer.Rows(1), 0)

      For Each d In documents
        If d.className = "infoHead" And d.innerText = "Filing Date" Then
          m_wsBuffer.Cells(2, iCol) = d.innerText
          Exit For
        End If
      Next d

      [additional, unrelated code that won't be added until I resolve the error]

    End If

  Next e

  Exit Sub

ErrorHandler:

  If Err.Number <> 91 Then
    Debug.Print "GetData: error " & Err.Number & ": " & Err.Description
    Resume Next
  End If

End Sub

Public Sub GetHTML(strUrl As String)

    'Debug.Print "GetHTML()"
    On Error GoTo ErrorHandler
    
    Dim x As MSXML2.XMLHTTP60
    Dim h As MSHTML.HTMLDocument
    Set x = New MSXML2.XMLHTTP60
    Set m_html = New MSHTML.HTMLDocument
    
    With x
        .Open "GET", strUrl, False
        .send
        If .readyState = 4 And .Status = 200 Then
            'Debug.Print "GetHTML: found html"
            m_html.body.innerHTML = .responseText
        Else
            Debug.Print "Error" & vbNewLine & "Ready state: " & .readyState & _
            vbNewLine & "HTTP request status: " & .Status
        End If
    End With
    
    Exit Sub
    
ErrorHandler:
    
    If Err.Number <> 91 Then
        Debug.Print "GetHTML: error " & Err.Number & ": " & Err.Description
        Resume Next
    End If

End Sub

事情是这样的：基于调试代码（为了便于阅读，我在上面大部分省略了），使用 GetHTML 子例程检索“e”循环中的第二个网页似乎很顺利；但似乎，当代码返回到循环的顶部时，它不会前进到集合中的下一个项目/链接，并且当 "If InStr(1, e.href, "index.htm", 1) > 0 And InStr(1, e.href, "archive", 1) > 0 Then" 行运行，代码抛出错误。我认为“权限被拒绝”与“e”变量的使用有关，因为如果我在“If InStr”行之前注释掉调试行，则其中一个错误会在调试窗口中消失。

关于为什么会发生这种情况的任何想法？我觉得我一定犯了一个令人麻木的简单错误；但我现在已经搞砸了一段时间，无法完全找到我的错误或发现这个问题在其他问题中得到了回答。

标签： excelvba