Code is returning the 0 Result instead of First search Link

Question

I have been using below code and it returns nothing except 0 results whereas it should paste the first search result into the Column B.

Any help and solution will be appreciated that why code is not working.

Data in column A

3P Pty Limited
BakPhysio
BD Rowa
BHC's Can View
Blooms The Chemist
5Odyassist Health And Wellness
Care Pharmaceuticals

Code

Sub XMLHTTP_Count()
    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html 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.XMLHTTP")
        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

    If html.getElementById("resultStats") Is Nothing Then
        str_text = "0 Results"
    Else
        str_text = html.getElementById("resultStats").innerText
    End If
        Cells(i, 2) = str_text
        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

Make sure that the code that you give in StackOverflow compiles. This is important. — Vityata, Oct 13 '21 at 12:04
This is potentially a duplicate question, look at this [answer](https://stackoverflow.com/questions/60653046/return-url-from-first-search-result?rq=1) and adept to your need. You can't really use xmlhttp to get google search result. — Raymond Wu, Oct 13 '21 at 12:31
I have tried this code before it just opens the first values search result in `Internet Explorer` and does nothing. Window remains open and when i close the `Internet Explorer` windows that error appears https://imgur.com/OSBHIjP on the line `Do While TypeName(.document.getElementById("res")) = "Null":` @Raymond Wu — Rajput, Oct 13 '21 at 13:07

score 1 · Answer 1 · answered Oct 13 '21 at 12:00

Google is not just a normal website, it has quite nice protection vs unwanted web-crawling.
Try to debug as much as you can, and see what html.body.innerHTML returns.
If you have done it you would have understood the reason of point 1.

Take a look at the code below. It prints the data of html.body.innerHTML and writes it in the third column. Furthermore, it provides you with the first <LI> of the html.body.innerhtml of the response from google.

Sub XmlHttpCount()
    
    Dim xmlHttp As Object
    Dim html As Object
    Dim i As Long
    Dim someText As String
    Dim url As String
    
    For i = 1 To 7
        url = "https://google.co.in/search?q=" & Cells(i, 1)

        Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        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
        
        Debug.Print html.body.innerhtml
        Cells(i, 3) = html.body.innerhtml
        
        
    If html.GetElementsByTagName("LI") Is Nothing Then
        someText = "0 Results"
    Else
        someText = html.GetElementsByTagName("LI")(0).InnerText
    End If
        Cells(i, 2) = someText
    Next
    
    Debug.Print "END"
    
End Sub

If you want to do something that actually works, then try to include the browser object and wait some seconds before trying the next loop, google may respond better.

I appreciate you answer @Vityata and you define it very well. But what i want is here https://imgur.com/LPA6UqA I just want the First Search Result `Link` in `column B` I have been struggling since a week but could not make it correct. I would appreciate if you could create a solution for this as well. I am just beginner to VBA not an expert to make these changes. I would greatly appreciate the help. @Vityata — Rajput, Oct 13 '21 at 12:18

Code is returning the 0 Result instead of First search Link

1 Answers1