vba - Internet Explorer VBA 自动化错误 : The object Invoked has disconnected from its clients

Question

我正在尝试编写从 Excel 读取值的代码，在基于 Web 的内部系统中查找它并将结果存储回 Excel。它读取 Excel 没有问题，打开 Internet Explorer 也没有问题，但是当我尝试引用已打开的内容时，出现上述错误。 “ie.Navigate url”行有效，但下一行“Set DOC = ie.Document”产生错误。关于导致这种情况的任何想法？这是我的代码:

Public Sub getClient()
  Dim xOpen As Boolean
  xOpen = False
  Dim row As Long

  Dim xL As Excel.Application
  Set xL = New Excel.Application
  xL.Visible = False
  Dim wb As Excel.Workbook
  Dim sh As Excel.Worksheet

  'Change the name as needed, out put in some facility to input it or 
  'process multiples...
  Dim filename As String
  filename = "auditLookup.xlsx"
  Set wb = xL.Workbooks.Open(getPath("Audit") + filename)
  xOpen = True
  Set sh = wb.Sheets(1)

  Dim ie As Variant
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True

  Dim DOC As HTMLDocument
  Dim idx As Integer
  Dim data As String

  Dim links As Variant
  Dim lnk As Variant
  Dim iRow As Long
  iRow = 2            'Assume headers

  Dim clientName As String
  Dim clientID As String
  Dim nameFound As Boolean
  Dim idFound As Boolean
  Dim url As String

  While sh.Cells(iRow, 1) <> ""
    'Just in case these IDs are ever prefixed with zeroes, I'm inserting 
    'some random character in front, but removing it of course when 
    'processing.
    url = "https://.../" +  mid(sh.Cells(iRow, 1), 2)
    ie.navigate url
    Set DOC = ie.Document

    'Search td until we find "Name:" then the next td will be the name. 
    'Then search for "P1 ID (ACES):" and the next td with be that.
    Set links = DOC.getElementsByTagName("td")
    clientName = ""
    clientID = ""
    nameFound = False
    idFound = False
    For Each lnk In links
        data = lnk.innerText
        If nameFound Then
            clientName = data
        ElseIf idFound Then
            clientID = data
        End If
        If nameFound And idFound Then
            Exit For
        End If

        If data = "Name:" Then
            nameFound = True
        ElseIf data = "P1 ID (ACES):" Then
            idFound = True
        End If
    Next
    sh.Cells(iRow, 2) = clientName
    sh.Cells(iRow, 2) = clientID
    iRow = iRow + 1
  Wend

  Set ie = Nothing
  If xOpen Then
    wb.Save
    Set wb = Nothing
    xL.Quit
    Set xL = Nothing
    Set sh = Nothing
    xOpen = False
  End If
Exit Sub

Answer 1

更改为:

Dim ie As InternetExplorer
Set ie = New InternetExplorerMedium
...

解决了问题。另外，我确实需要重新添加评论中提到的 Do 循环:

Do
    DoEvents
Loop Until ie.ReadyState = READYSTATE_COMPLETE