Doing a Google search from within Excel

[HedgePig]

I am wanting to loop through a list of search terms and find out how many hits each one produces in google. I thought it was my lucky day when I found an article at http://www.eggheadcafe.com/tutorial...fe-7764a1096708/how-to-get-google-search.aspx which described exactly what I want to do.
<o> </o>
However, I can’t get the code to run.

  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   'This seems to work
  search_http.send                                    'The send method fails.

<o> </o>
I’ve tried this both at home and at work but the send method always generates an error:
“Run-time error -21466977211 (800c0005)
The system cannot locate the resource specified.”
<o> </o>
Does anybody know what is causing this? I’m really out of my depth here as I’ve never worked with the MSXML2.XMLHTTP object nor do I know anything about it. A web search suggested that using Microsoft.XMLHTTP might be better than MSXML2 but I get exactly the same result.
<o> </o>
I’d appreciate any suggestions regarding this particular problem or indeed any alternative solutions for how to send a query to google from within Excel vba and then pull out specific information.
<o> </o>
Regards
HedgePig

P.S. Am using Office 2007 and Windows XP.

 

[BrianB]

I copy/pasted the code from egghead cafe and it runs perfectly without any amendment.

The code you posted is not much good because it is incomplete.

You need to step through the code a line at a time using F8 key and check the variables values by hovering the mouse pointer over them.

My guess is that you have not put a search in cell A1 of Sheet 1 (?)

Also we have to wait some time while it runs. Observe how long an ordinary Google search takes and multiply by the number of rows.

 

[Beate Schmitz]

Hello,

pls try:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><b><span style="color:#000080"; >Public</span> <span style="color:#000080"; >Sub</span> ExcelGoogleSearch()</b><br />    <span style="color:#000080"; >Dim</span> searchwords <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span><br />    <span style="color:#000080"; >Dim</span> search_url <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span><br />    <span style="color:#000080"; >Dim</span> search_http <span style="color:#000080"; >As</span> <span style="color:#000080"; >Object</span><br />    <span style="color:#000080"; >Dim</span> results_var <span style="color:#000080"; >As</span> Variant, NumberOfResults <span style="color:#000080"; >As</span> <span style="color:#000080"; >Variant</span><br />    <span style="color:#000080"; >Dim</span> rowcount <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span><br />    <span style="color:#000080"; >Dim</span> pos_1 <span style="color:#000080"; >As</span> Integer, pos_2 <span style="color:#000080"; >As</span> Integer, pos_3 <span style="color:#000080"; >As</span> <span style="color:#000080"; >Integer</span><br />    <span style="color:#000080"; >With</span> Sheets(<span style="color:#800000"; >"Sheet1"</span>)<br />        .Columns(2).ClearContents<br />        rowcount = 1<br />        <span style="color:#000080"; >Do</span> <span style="color:#000080"; >While</span> .Range(<span style="color:#800000"; >"A"</span> & rowcount) <> ""<br />            searchwords = .Range(<span style="color:#800000"; >"A"</span> & rowcount).Value<br />            <span style="color:#008000"; >' Get keywords and validate by adding + for spaces between</span><br />            searchwords = Replace$(searchwords, <span style="color:#800000"; >" "</span>, <span style="color:#800000"; >"+"</span>)<br />            <span style="color:#008000"; >' Obtain the source code for the Google-searchterm webpage</span><br />            search_url = <span style="color:#800000"; >"http://www.google.com/search?hl=en&q="</span> & searchwords & <span style="color:#800000"; >"&meta="""</span><br />            <span style="color:#000080"; >Set</span> search_http = CreateObject(<span style="color:#800000"; >"MSXML2.XMLHTTP"</span>)<br />            search_http.Open <span style="color:#800000"; >"GET"</span>, search_url, <span style="color:#000080"; >False</span><br />            search_http.send<br />            results_var = search_http.responsetext<br />            <span style="color:#000080"; >Set</span> search_http = <span style="color:#000080"; >Nothing</span><br />            <span style="color:#008000"; >' Find the number of results and post to sheet</span><br />            pos_1 = InStr(1, results_var, <span style="color:#800000"; >"b> of"</span>, vbTextCompare)<br />            pos_2 = InStr(3 + pos_1, results_var, <span style="color:#800000"; >">"</span>, vbTextCompare)<br />            pos_3 = InStr(pos_2, results_var, <span style="color:#800000"; >"<"</span>, vbTextCompare)<br />            NumberOfResults = <span style="color:#000080"; >Mid</span>(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))<br />            Range(<span style="color:#800000"; >"B"</span> & rowcount) = NumberOfResults<br />            rowcount = rowcount + 1<br />        <span style="color:#000080"; >Loop</span><br />    <span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span><br /><b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></b><br /></span></nobr></div>

 

[mikerickson]

Hedge, what version of Excel are you using?

 

[wigi]

Hedge, what version of Excel are you using?

Last line in the original post: Excel 2007

 

[wigi]

Nice code from the eggheadcafe website, but this does the same and is easier and shorter:

Public Sub ExcelGoogleSearch()
    Columns(2).ClearContents
    For Each r In Columns(1).SpecialCells(2, 2)
        With CreateObject("MSXML2.XMLHTTP")
            .Open "GET", "http://www.google.com/search?hl=en&q=" & Replace(r.Text, " ", "+") & "&meta=""", False
            .send
            r.Offset(, 1) = Split(Split(.responsetext, "</b> of about <b>")(1), "</b>")(0)
        End With
    Next
End Sub

 

[jeffreytp]

I am wanting to loop through a list of search terms and find out how many hits each one produces in google. I thought it was my lucky day when I found an article at http://www.eggheadcafe.com/tutorial...fe-7764a1096708/how-to-get-google-search.aspx which described exactly what I want to do.
<?xml:namespace prefix = o /><o></o>
However, I can’t get the code to run.

  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   'This seems to work
  search_http.send                                    'The send method fails.

<o></o>
I’ve tried this both at home and at work but the send method always generates an error:
“Run-time error -21466977211 (800c0005)
The system cannot locate the resource specified.”
<o></o>
Does anybody know what is causing this? I’m really out of my depth here as I’ve never worked with the MSXML2.XMLHTTP object nor do I know anything about it. A web search suggested that using Microsoft.XMLHTTP might be better than MSXML2 but I get exactly the same result.
<o></o>
I’d appreciate any suggestions regarding this particular problem or indeed any alternative solutions for how to send a query to google from within Excel vba and then pull out specific information.
<o></o>
Regards
HedgePig

P.S. Am using Office 2007 and Windows XP.

I am having the exact same problem. Anyone have a solution?