Web Query # of Google Search Results

[muckfaster]

Hi Everyone,

In one column, I have a long list of keywords.

I'd like to automatically return the number of Google search results for each of those words in the adjacent column.

The "# of search results" text is not in a table format, however, so I'm not quite sure how to access that.

Any thoughts on how to automate this?

Thanks!

- jack

[SDruley]

http://vbadud.blogspot.com/2008/05/g...using-vba.html

[ShredDude]

Try this as a UDF in the column you want the answers in, referring to the search term as the passed arguemnt

For example: in B1,

Please Login or Register  to view this content.

Insert this code in a Standard Module in the workbook...

Please Login or Register  to view this content.

[muckfaster]

That worked perfectly... thank you so much!

- jack

[muckfaster]

The UDF method seems to work, but tends to return errors after a few iterations.

After a few uses, it starts returning a #VALUE! error. If I reboot the machine, it will work again a few times.

Does the UDF eat up memory each time it's used? Any thoughts on how to correct it?

Thanks again for all your help!

- jack

[ShredDude]

the original UDF Code did not have any error checking. the modified code below performs a simple check to confirm if the needed element is found. Try using this and see what happens.

If you're getting an error on a search term that once produced an accurate result, that's probably a different problem. If that happens, post a sample of the terms you're searching and I'll explore it.

Please Login or Register  to view this content.

[muckfaster]

Thanks very much.

It's strange... terms that once worked will fail after using the UDF a few times.

I'm searching terms like "taco" and "coffee." It doesn't seem like it's the term itself, but something else that generally causes all terms to fail after a while.

A reboot of the computer seems to fix this, but then it goes back to generating errors again.

Could this be related to internet speed / bandwith? It seems like it sometimes runs very fast, and other times it takes forever -- and then returns the error.

Thanks again for the great help!

- jack

[ShredDude]

I don't know. I just tested it by changing the search terms in two cells several times. It never failed for me.

[muckfaster]

Okay, it looks like the errors were generated because Google starts blocking queries after several iterations.

I think the only solution is to have it error check, and then introduce a wait timer of 20 or 30 minutes if doesn't return a valid result. I believe Google unflags you after 15 minutes or so.

I've got roughly 50,000 queries to run, so this might take a while doing the above.

Any suggetsions on a more efficient way?

[ShredDude]

So, does it now return "Not Found" for the search terms it fails on?

If so, you might want to try to confirm the point at which the problem occurs with something like the little Test sub below.

Paste all of this code into your module, replacing the getNumgoogResults function. Note the slight mod in there to Delete the URL cache each attempt, This will force it to research every time even it's the same search term.

I'm not going to run it on my bandwidth. I'll be interested in what you find....

Please Login or Register  to view this content.

[muckfaster]

After a few iterations, it generates an "access is denied" error if I try running the test (so the count didn't actually display).

The error is triggered by the .send command in the UDF.

I'll update the test to have it track good returns to see where the error pops.... probably need to wait 30 mins to get unblocked.

Do you think putting in a wait timer is the best option?

[muckfaster]

Just tried it and it got up to 189, and then the access denied error.

Seems like it then locks me out from anywhere between 10 and 30 minutes.

I'm guessing it was maybe a minute of queries to get to 189... so maybe they do a 60 second test against DOS attacks?

[herac6969]

The #value kept happening to me and then I noticed when I am logged into google they redirect and request to go to http://www.google.com to https://www.google.com
as soon as I logged out of google my spreadsheet comes to life.

here is the update for anyone who wants to use that code on a 64bit machine

Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "wininet.dll" _ Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As LongLong Public Function getNumGoogResults(strSearch As String) As String 'Retrieve the # of Results returned by google for a given Search term Dim x As String, s As String Dim xmlhttp As Object Dim strURL As String strURL = "http://www.google.com/search?q=" & strSearch DeleteUrlCacheEntry strURL Set xmlhttp = CreateObject("msxml2.xmlhttp") With xmlhttp .Open "Get", strURL, False .send x = .responsetext End With Set xmlhttp = Nothing s = "resultStats>" If InStr(1, x, s) > 0 Then x = Mid(x, InStr(1, x, s) + Len(s)) getNumGoogResults = Left(x, InStr(1, x, "<") - 1) Else getNumGoogResults = "Not Found" End If End Function Public Sub testGoogBlock() Dim n As Long Dim x As String For n = 1 To 100000 x = getnumgoogresults("taco") If x = "Not Found" Then MsgBox ("Google search failed after " & n & " attempts.") Exit For End If Next n