로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
Excel Macro VBA to Count Google Search Results

问题 Excel Macro VBA to Count Google Search Results

  • 2012년 6월 7일 목요일 오후 6:51
     
      코드 있음
    로그인하여 투표
    0

    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월 8일 금요일 오전 7:31
    중재자
     
     
    로그인하여 투표
    0

    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일 금요일 오후 1:12
     
     
    로그인하여 투표
    0

    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일 금요일 오후 6:36
     
     
    로그인하여 투표
    0
    Loop through web queries and extract the values you want from the queries.
     
  • 2012년 6월 8일 금요일 오후 9:45
     
     
    로그인하여 투표
    0
    Loop through web queries and extract the values you want from the queries.

    Can you provide the Macro code for this?

    Jeff

     
  • 2012년 8월 24일 금요일 오후 6:33
     
     
    로그인하여 투표
    0

    was there any answer to this?