Run Time Error '91' : Object Variable or With Block not Set

Question

I have been using below code and it is giving me an error that is Run Time Error '91' : Object Variable or With Block not Set I do not know why.

Earlier it was working fine but i do not know why an error is occur. I have tried other solutions searching Copy URL from the first Search but they also were not working.

error appear on this line Set link = ecoll.getElementsByTagName("a")(0)

If someone can provide alternate solution it will be great help. Any help will be appreciated.

Sub link()
    Dim doc As HTMLDocument
    Set doc = New HTMLDocument
    
    Dim lastrow As Long
    Dim ecoll As Object
    Dim link As Object
    Dim t As Date
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    t = Now()
    
    Dim reqObj As Object
    Set reqObj = CreateObject("MSXML2.XMLHTTP")
        
    For i = 2 To lastrow
            
        reqObj.Open "GET", "https://www.google.co.in/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000), False
        reqObj.send
        
        doc.body.innerHTML = reqObj.responseText
        
    
        Set ecoll = doc.getElementById("rso")
        Set link = ecoll.getElementsByTagName("a")(0)
            
        Cells(i, 2) = link.href
    Next
    
    Set doc = Nothing
    Set reqObj = Nothing
    
    Debug.Print "done" & "Time taken : " & Format(Now() - t, "hh:mm:ss")
    MsgBox "Ellapsed Time - " & Format(Now() - t, "hh:mm:ss")
End Sub

So the document does not contain an element with id "rso", or the element with id "rso" does not contain any ``s. Which is either because they are not actually there, or because you don't give the document the [time to parse it](https://stackoverflow.com/q/23299134/11683). — GSerg, Oct 12 '21 at 16:01
Maybe try to add in a delay before parsing. @GSerg might just be onto something. Oh, and would you please press ‘Debug’ to show on which line the code breaks? — Havard Kleven, Oct 12 '21 at 16:21
Is the error happening on the exact same site it was working for previously? And I mean exact same, as @GSerg is saying, the HTML may have changed. When it's not working, can you confirm it has the tag you're trying to capture? — BruceWayne, Oct 12 '21 at 16:45
Alright so this is the issue that its giving an error because it does not find that value in cell. I have tried to used the parse but i am on mistake, If it does not bother to you can you please share the solution. I would appreciated the help. @GSerg — Rajput, Oct 12 '21 at 16:51
on this line an error is appear `Set link = ecoll.getElementsByTagName("a")(0)` — Rajput, Oct 12 '21 at 16:52
when i was using this code earlier there was a different data set and now data is different, i understand the point of GSerg but unable to make the code correct. @BruceWayne — Rajput, Oct 12 '21 at 16:53
I cannot make it up i really do not understand what is the problem. I have tried all the available solution on the web but none of them is working. I hope you can help @Raymond Wu — Rajput, Oct 15 '21 at 12:34

QHarr · Answer 1 · 2021-10-16T09:43:33.863

2

There is cookie consent required, or else you get don't get the expected search results; and the html, at least for me, is different. It was sufficient for me to add the following headers:

reqObj.setRequestHeader "cookie", "CONSENT=YES+"
reqObj.setRequestHeader "User-Agent", "Mozilla/5.0"

Then I needed a different ID of:

Set ecoll = doc.getElementById("main")

Your mileage may vary.

Then you need to target the a tags with more discrimination, or you will get a lot of stuff you almost certainly don't want.

So, try removing:

Set link = ecoll.getElementsByTagName("a")(0)

And then use this:

Cells(i, 2) = Replace$(ecoll.querySelector("[href*=url]").href, "about:/url?q=", vbNullString)

edited Oct 16 '21 at 09:43

answered Oct 15 '21 at 18:26

QHarr

83,427
12
54
101

Same problem is appearing, but it is giving some search result but that is not web link as i need. @QHarr https://imgur.com/a/CF4OiOH – Rajput Oct 15 '21 at 18:45
Try the above edit. – QHarr Oct 15 '21 at 19:31
Apologies for being late, But when i added these changes to code problem was still same error was appearing on this line. `Cells(i, 2) = Replace$(ecoll.querySelector("[href*=url]").href, "about:/url?q=", vbNullString)` https://imgur.com/IOb5JZJ – Rajput Oct 18 '21 at 10:59
You didn't change the line above. `Set ecoll = doc.getElementById("main")` – QHarr Oct 18 '21 at 11:00
Sir error is popping up on the same line https://imgur.com/YSARXOZ – Rajput Oct 18 '21 at 11:06
You need to click debug and show the highlighted line. My guess is you are using a very old Office version. I ran the code and it worked fine. – QHarr Oct 18 '21 at 18:13
https://imgur.com/undefined Sir here is the line where error is occur. I am using Excel 2016 – Rajput Oct 20 '21 at 13:48
Link doesn't work – QHarr Oct 20 '21 at 19:56

AziMez · Accepted Answer · 2021-10-18T16:20:39.797

Hope this help you

Firstly, It's appear that the html contenent in reqObj.responseText does not contain any element with id "rso" at all.

Further more, the response coming to vba from "https://www.google.co.in/search?q=" is not the same that presented in browser.

So, I try to do some tricks to catch the first search result showed on google.

For example, with the keyword "BakPhysio" we get this one.

At this point we can get the link Description in the using

.getElementsByTagName("H3")(0).innerText

In the other hand, URL link is located near href section, we catch it using substring between "url?q=" and "&"

This following VBA code should give you some results.

Sub link()
    Dim doc As HTMLDocument
    Set doc = New HTMLDocument
    
    Dim lastrow As Long
    Dim ecoll As Object
    Dim Link As Object
    Dim t As Date
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    t = Now()
    
    Dim reqObj As Object
    Set reqObj = CreateObject("MSXML2.XMLHTTP")
        
    For i = 2 To lastrow
        reqObj.Open "GET", "https://www.google.co.in/search?q=" & _
        Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000), False
        reqObj.send
                
        doc.body.innerHTML = reqObj.responseText
        
        
        
      Set all_link = doc.getElementsByTagName("A")
      Dim html, description, Url As String
      Dim start_position, end_position As Integer
      

           For j = 1 To 20 'all_link.Length - 1
              html = all_link(j).outerHTML
              
              If InStr(LCase(html), LCase("/url?q=")) Then
               start_position = InStr(html, "http")
               end_position = InStr(html, "&")
               Url = Mid(html, start_position, end_position - start_position)
                
                    If InStr(LCase(html), LCase("<h3")) Then
                      description = all_link(j).getElementsByTagName("h3")(0).innerText
                      MsgBox description & vbNewLine & Url
                      Cells(i, 2) = Url
                      j = 20 ' Once catching the 1st link, FOR loop is skipped
                End If
              End If
    
           Next j
    Next i
    
    Set doc = Nothing
    Set reqObj = Nothing
    
    Debug.Print "done" & "Time taken : " & Format(Now() - t, "hh:mm:ss")
    MsgBox "Ellapsed Time - " & Format(Now() - t, "hh:mm:ss")

End Sub

[Result]

Thank you but i am receiving these links instead of as your picture have https://imgur.com/m4p99ax — Rajput, Oct 18 '21 at 11:02
@Rajput, Thanks for the feedback. I fixed this issue, the code is EDITED by adding this line: j = 20 ' Once catching the 1st link, FOR loop is skipped. — AziMez, Oct 18 '21 at 16:22

Run Time Error '91' : Object Variable or With Block not Set

2 Answers2