VBA运行时错误'91'对象变量或未设置块变量 [英] VBA Run-time Error '91' Object-variable or With block variable not set

问题描述

我有大约12,000行数据需要网站.此VBA代码可以正常运行约800次，然后停止.现在由于此错误，我无法使其再次运行.我不知道如何重新运行它.

I have about 12,000 rows of data that I need websites for. This VBA code was working fine for about 800 and then stopped. Now I can't get it to run again due to this error. I can't figure out how to get it running again.

运行时错误'91'对象变量或未设置块变量

Run-time Error '91' Object-variable or With block variable not set

Sub XMLHTTP()

    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
    Dim start_time As Date
    Dim end_time As Date

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim cookie As String
    Dim result_cookie As String

    start_time = Time
    Debug.Print "start_time:" & start_time

    For i = 2 To lastRow

        url = "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

        Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.setRequestHeader "Content-Type", "text/xml"
        XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
        XMLHTTP.send

            Set html = CreateObject("htmlfile")
        html.body.innerHTML = XMLHTTP.ResponseText
        Set objResultDiv = html.getelementbyid("rso")
        Set objH3 = objResultDiv.getelementsbytagname("H3")(0)
        Set link = objH3.getelementsbytagname("a")(0)


        str_text = Replace(link.innerHTML, "<EM>", "")
        str_text = Replace(str_text, "</EM>", "")

        Cells(i, 2) = str_text
        Cells(i, 3) = link.href
        DoEvents
    Next

    end_time = Time
    Debug.Print "end_time:" & end_time

    Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
    MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub

推荐答案

运行时错误'91'对象变量或未设置块变量

Run-time Error '91' Object-variable or With block variable not set

由于以下SET语句之一失败，您将收到该错误.

You are getting that error because one of the below SET statement is failing.

Set objResultDiv = html.getelementbyid("rso")
Set objH3 = objResultDiv.getelementsbytagname("H3")(0)
Set link = objH3.getelementsbytagname("a")(0)

您需要正确处理对象.例如

You need to properly handle the objects. For example

Set objResultDiv = HTML.getelementbyid("rso")

If objResultDiv Is Nothing Then
    MsgBox "Id `rso` not found for " & Cells(i, 1)
Else
    Set objH3 = objResultDiv.getelementsbytagname("H3")(0)

    If objH3 Is Nothing Then
        MsgBox "Tag `H3` not found for " & Cells(i, 1)
    Else
        Set link = objH3.getelementsbytagname("a")(0)

        If link Is Nothing Then
            MsgBox "Tag `a` not found for " & Cells(i, 1)
        Else
            '
            '~~> Rest of your code
            '
        End If
    End If
End If

这篇关于VBA运行时错误'91'对象变量或未设置块变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！