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

问题描述：

我有大约12,000行数据，我需要的网站。这个VBA代码工作正常约800，然后停下来。现在由于这个错误我无法再次运行。我无法弄清楚如何让它再次运行。VBA运行时错误'91'对象变量或块变量未设置

运行时错误“91”对象的变量或带块变量未设置

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

'URL = “https://www.google.co.in/search?q=” ＆细胞（I，1）＆ “＆RND =” ＆WorksheetFunction.RandBetween（1，10000） '你在破解网站吗？ – findwindow

我不知道你的意思是“破解网站” 我应该提到，我从这个网页找到了这段代码：http://*.com/questions/17495644/using-vba-in-excel-to- google-search-in-ie-and-return-the-first-of-the-first – Brayheart

你在谷歌搜索随机页面（RandBetween）12k次。这是饼干做的事情。只是想确定。 – findwindow

答

运行时错误“91”对象的变量或带块变量未设置

您正在收到该错误，因为下面的SET语句之一失败。

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

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

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'对象变量或块变量未设置

相关推荐