Hi!

I'd like to access Google search from Excel to determine page rankings -
can anyone point me in the right direction, please?

I tried direct HTML access, but Google seems to detect and block this
after a few requests, so I'd like to use the AJAX API.

Thanks and best regards
Martin

Thsi code gets the number of results for a search at google.

Public Sub GoogleSearch()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
Dim ie As Object 'InternetExplorer

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

With Sheets("Sheet1")
RowCount = 2
Do While .Range("B" & RowCount) < ""

szSearchWords = .Range("B" & RowCount).Value


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


ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents

Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
'really item 3, but arrays staarts at 0
NumberofResults = itm.Children.Item(2).innertext
.Range("C" & RowCount) = NumberofResults
Exit For
End If
Next itm
RowCount = RowCount + 1
Loop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub






"Martin Schneider" wrote:

Hi!

I'd like to access Google search from Excel to determine page rankings -
can anyone point me in the right direction, please?

I tried direct HTML access, but Google seems to detect and block this
after a few requests, so I'd like to use the AJAX API.

Thanks and best regards
Martin

Joel schrieb:
Thsi code gets the number of results for a search at google.

Hi, Joel,

thanks for the idea. Unfortunately this triggers the anti-spam-mechanism
at Google as well. I'd like to try the official way, do you have code
for this as well?

Thanks and best regards,
Martin

I did something similar to this a couple of months ago. I don't know HTML
but I helped somebody use the google tanslation API

see posting

http://www.microsoft.com/office/comm...xp=&sloc=en-us


Joe wrote HTML code to use the translation API and I wrote the code to run
the html and put the data in a speadsheet.

See if this code causes the anti-spam to trigger. If it doesn't then we can
do something similar for your API.

"Martin Schneider" wrote:

Joel schrieb:
Thsi code gets the number of results for a search at google.

Hi, Joel,

thanks for the idea. Unfortunately this triggers the anti-spam-mechanism
at Google as well. I'd like to try the official way, do you have code
for this as well?

Thanks and best regards,
Martin

Joel schrieb:
I did something similar to this a couple of months ago. I don't know HTML
but I helped somebody use the google tanslation API

see posting

http://www.microsoft.com/office/comm...xp=&sloc=en-us

Hi, Joel,

thanks for the link. It pointed me into the right direction. Of course
now I have another question (which of course isn't strictly Excel
anymore, but I hope I may ask this as well).

http://ajax.googleapis.com/ajax/serv...1.0&q=question

gives me search results from google.com - is there a way to get results
from google.de? I was unable to find a corresponding parameter...

Thanks and best regards,
Martin

I don't know. I will search around the web later but not sure if I will find
anything.

"Martin Schneider" wrote:

Joel schrieb:
I did something similar to this a couple of months ago. I don't know HTML
but I helped somebody use the google tanslation API

see posting

http://www.microsoft.com/office/comm...xp=&sloc=en-us

Hi, Joel,

thanks for the link. It pointed me into the right direction. Of course
now I have another question (which of course isn't strictly Excel
anymore, but I hope I may ask this as well).

http://ajax.googleapis.com/ajax/serv...1.0&q=question

gives me search results from google.com - is there a way to get results
from google.de? I was unable to find a corresponding parameter...

Thanks and best regards,
Martin

Joel schrieb:
I don't know. I will search around the web later but not sure if I will find
anything.

Thanks for your efforts. In the meantime I learned that this feature is
in the pipeline... :-)

Best regards
Martin

On Jul 16, 8:43*am, Martin Schneider <martin.schnei...@illusion-
factory.de wrote:
Joel schrieb:

I don't know. *I will search around the web later but not sure if I will find
anything.

Thanks for your efforts. In the meantime I learned that this feature is
in the pipeline... :-)

Best regards
Martin

As an aside, I scripted a Google search the other day and would get
automatically booted off after about 200 consecutive searches (in a
loop of 1,300). (I got that page stating that I looked like a virus
and I needed to insert the text from the picture in order to continue
searching). I resolved the issue by deleting the google related
search cookies in the Temporary Internet Files folder after each 200th
time through the loop.

Best,

Matthew Herbert

Matthew Herbert schrieb:
On Jul 16, 8:43 am, Martin Schneider <martin.schnei...@illusion-
factory.de wrote:
Joel schrieb:

I don't know. I will search around the web later but not sure if I will find
anything.
Thanks for your efforts. In the meantime I learned that this feature is
in the pipeline... :-)

Best regards
Martin

As an aside, I scripted a Google search the other day and would get
automatically booted off after about 200 consecutive searches (in a
loop of 1,300). (I got that page stating that I looked like a virus
and I needed to insert the text from the picture in order to continue
searching). I resolved the issue by deleting the google related
search cookies in the Temporary Internet Files folder after each 200th
time through the loop.

Hi, thanks! That sounds interesting!

Do you script the Internet Explorer or use the Microsoft.XMLHTTP object?
(Or maybe this doesn't matter as the IE does this, too :-))

Thanks and best regards,
Martin

On Jul 17, 12:22*am, Martin Schneider <martin.schnei...@illusion-
factory.de wrote:
Matthew Herbert schrieb:





On Jul 16, 8:43 am, Martin Schneider <martin.schnei...@illusion-
factory.de wrote:
Joel schrieb:

I don't know. *I will search around the web later but not sure if I will find
anything.
Thanks for your efforts. In the meantime I learned that this feature is
in the pipeline... :-)

Best regards
Martin

As an aside, I scripted a Google search the other day and would get
automatically booted off after about 200 consecutive searches (in a
loop of 1,300). *(I got that page stating that I looked like a virus
and I needed to insert the text from the picture in order to continue
searching). *I resolved the issue by deleting the google related
search cookies in the Temporary Internet Files folder after each 200th
time through the loop.

Hi, thanks! That sounds interesting!

Do you script the Internet Explorer or use the Microsoft.XMLHTTP object?
* (Or maybe this doesn't matter as the IE does this, too :-))

Thanks and best regards,
Martin- Hide quoted text -

- Show quoted text -

Martin,

I scripted IE (much like the code Joel provided in the thread) and
then used the FileSystemObject to delete the Google search cookies in
order to prevent the Google "error" page from appearing. (In order to
get a sense of timing, it would take about 8 minutes to loop through
the ~1,300 searches, but this time includes the time it took to delete
the cookies every 200th time and the time to "get" the data off the
search page). If I'm getting stock price information off of say
Yahoo!, then I use XMLHTTP because it is much faster than scripting
IE. (But then again, I don't find myself automating the Internet that
often either).

Best,

Matt