-4

I am getting the below error when i run the macro in excel...

Run-time error '91': Object variable or with block variable not set

The error occurs on the line str_text = Replace(link.innerHTML, "<EM>", "")

{
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
}

Can any one give solution for this?

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Arun Santha Kumar
  • 1
  • 1
  • 1
    @Arun - The first step of troubleshooting a VBA issue is to make sure all of your variables are properly declared (which they are not, you're missing two). Add `Option Explicit` to the top of [every] module [forever] so VBA will "force" you to properly declare & refer to variables, objects, properties, etc. Also, why the {curly braces} around your code? – ashleedawg Mar 31 '18 at 11:07
  • Is this a valid statement? `Set html = CreateObject("htmlfile")` – ashleedawg Mar 31 '18 at 11:11
  • 3
    @ashleedawg yes, because he's late-bound `"MSXML2.serverXMLHTTP"` - see [here](https://stackoverflow.com/questions/20495035/vba-what-to-reference-for-htmlfile) – Chronocidal Mar 31 '18 at 11:13
  • @Chronocidal - ah yes, thx, I'm used to seeing it capitalized properly. – ashleedawg Mar 31 '18 at 11:23
  • Try `Set html = CreateObject("HTMLDocument")` instead. That's the way I write my own xmlhttp code. –  Mar 31 '18 at 11:24
  • Possible duplicate of https://stackoverflow.com/questions/36901737/vba-run-time-error-91-object-variable-or-with-block-variable-not-set (based on identical answer code at https://stackoverflow.com/questions/17495644/using-vba-in-excel-to-google-search-in-ie-and-return-the-hyperlink-of-the-first) – T.M. Mar 31 '18 at 12:26
  • Possible duplicate of [VBA Run-time Error '91' Object-variable or With block variable not set](https://stackoverflow.com/questions/36901737/vba-run-time-error-91-object-variable-or-with-block-variable-not-set) – T.M. Mar 31 '18 at 12:28

2 Answers2

0

You have a number of problems with your code.

  • You didn't let us know where the error was occurring.

  • As Chronocidal pointed out, your procedure name is the same as a variable name... not good.

  • You also have 2 variables undeclared and 2 other variables declared but not used. Add Option Explicit to the top of [every] module [forever] so VBA will "force" you to properly declare & refer to variables, objects, properties, etc.

  • For some reason there are {Curly Braces} around your code.

These are careless oversights showing lack of attention to detail, but I think what's causing the error in this case is the "query string data" on your worksheet (starting in cell A2). One or more of the cells are either blank or contain characters that the Google query "doesn't like".

You can determine which ones by adding this line: MsgBox url ***just after* the line with url = "https://www.... line.**

When the message box pops up, look at the query string and see if it's missing something, specifically right after q=.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    Don't forget that google temporarily suspends searches from your ip address if you get too greedy. –  Mar 31 '18 at 12:00
-1

Your Sub has the same name as one of your Objects: XMLHTTP - Excel probably can't tell what's what, because you have two things with the same name.

Also, you haven't declared str_text or i

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • did you test this solution? There's more than one variable not declared, and there are other issues. – ashleedawg Mar 31 '18 at 11:08
  • The machine I'm on at the moment doesn't have Excel installed, but I've used Notepad++ to match variables against declarations three times now: `str_text` is the only variable I couldn't find a declaration for, but declared variables `cookie` and `result_cookie` are not used anywhere – Chronocidal Mar 31 '18 at 11:14
  • 1
    `i` is also not declared. He's not coming back anytime soon anyhow, and when he does, he's expecting his code to be fully debugged. (Does anyone else see irony in that he's using this code to query Google? lol) – ashleedawg Mar 31 '18 at 11:21
  • Ah, thanks - not sure how *I* missed that one. :P Adding to the answer. – Chronocidal Mar 31 '18 at 11:22