none
Excel Macro VBA to Count Google Search Results

    질문

  • I have a keyword in A1, I want to run a Google search of A1 and count the results and put that # in B1.

    For Example, if A1= how to make small seats smaller. The Google Search result count is 140,000,000. I want to put 140,000,000 into B1, repeat for all the values in column A. Here is a Macro code I found, but when I run it I get this Error: Run-time error '-2147024891 (80070005)': Access is denied. When I debug, it highlights: search_http.Send

    Public Sub ExcelGoogleSearch()
        Dim searchwords As String
        Dim search_url As String
        Dim search_http As Object
        Dim results_var As Variant, NumberOfResults As Variant
        Dim rowcount As Long
        Dim pos_1 As Integer, pos_2 As Integer, pos_3 As Integer
        With Sheets("Sheet1")
            .Columns(2).ClearContents
            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 = "http://www.google.com/search?hl=en&q=" & 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, "b> of", vbTextCompare)
                pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
                pos_3 = InStr(pos_2, results_var, "<", 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

    I am running Excel 2010 & IE9

    Anyone have a solution?

    2012년 6월 7일 목요일 오후 6:51

모든 응답

  • Hi,

    First, I copied your code and run, there is no error in my computer, but there is no result in column B.

     Second, while debugging, I found the pos_1 is 0, and based on my research, there are three situations that the InStr() function will return 0:

    • String1 is zero length or Nothing
    • String2 is not found
    • Start > String2

    More information about InStr() function, please refer to the link:

    http://msdn.microsoft.com/en-us/library/8460tsh1(v=VS.80).aspx

    And the String1(results_var) has the value while debugging, so that is to say String2 (“b> of”) is not found. So that is what I think:

    Are you sure the count of results is behind the “b> of” string?

    Third, while debugging, the NumberOfResults returns null, because “(-1 + pos_3 - pos_2)” returns 0.


    Jaynet Zhang

    TechNet Community Support

    2012년 6월 8일 금요일 오전 7:31
  • Hi,

    First, I copied your code and run, there is no error in my computer, but there is no result in column B.

     Second, while debugging, I found the pos_1 is 0, and based on my research, there are three situations that the InStr() function will return 0:

    • String1 is zero length or Nothing
    • String2 is not found
    • Start > String2

    More information about InStr() function, please refer to the link:

    http://msdn.microsoft.com/en-us/library/8460tsh1(v=VS.80).aspx

    And the String1(results_var) has the value while debugging, so that is to say String2 (“b> of”) is not found. So that is what I think:

    Are you sure the count of results is behind the “b> of” string?

    Third, while debugging, the NumberOfResults returns null, because “(-1 + pos_3 - pos_2)” returns 0.


    Jaynet Zhang

    TechNet Community Support

    Thank you for the reply. So what code would be correct to make the macro work?

    This is what I am looking to do:

    I have a keyword in A1, I want to run a Google search of A1 and count the results and put that # in B1.

    For Example, if A1= how to make small seats smaller.

    The Google Search result count is 140,000,000.

    I want to put 140,000,000 into B1, repeat for all the values in column A.


    Jeff

    2012년 6월 8일 금요일 오후 1:12
  • Loop through web queries and extract the values you want from the queries.
    2012년 6월 8일 금요일 오후 6:36
  • Loop through web queries and extract the values you want from the queries.

    Can you provide the Macro code for this?

    Jeff

    2012년 6월 8일 금요일 오후 9:45
  • was there any answer to this?

    2012년 8월 24일 금요일 오후 6:33