ExcelGoogleSearch

[julian_fr84]

Please can anybody help me with this vba code. i am extracting google search results from excel weblinks. i have create column A for links and B for results but when macro starts working stopped when there is no results for that link. what can i do to go to the next cell automatically in column A and make the result in Column B = 0 instead of stopping.

Public Sub ExcelGoogleSearch()


Dim searchWords As String


With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value


' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")


' Obtain the source code for the Google-searchterm webpage
search_url = "https://www.google.com/search?hl=en&tbm=shop&q=site:" & searchWords & "&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing


' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<nobr>", vbTextCompare)
NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))
Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Try modifying this part of the code to read....

    ' Find the number of results and post to sheet
    pos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)
    If pos_1 = 0 Then
        NumberofResults = 0
    Else
        pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
        pos_3 = InStr(pos_2, results_var, "[B][COLOR="#0000CD"]<[/COLOR][/B] ", vbTextCompare) 'remove space after <
        NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))
    End If
    Range("B" & RowCount) = NumberofResults
    RowCount = RowCount + 1

Loop

In your posted code, I believe there is a "< " symbol missing. Sometimes the forum software misinterprets that symbol as a special HTML character.
Remove the extra space that I added to test the code.

 

[julian_fr84]

Thanks a lot, works fine.