Using VBA in Excel to Google Search in IE and return the 5 or 10 results

Question

Is it possible to modify the code found in this thread: "Using VBA in Excel to Google Search in IE and return the hyperlink of the first result" to get the first 5 or 10 hyperlinks? I am a novice at VBA and not very familiar with the object model used in this code.

thanks much

Rusty

I am trying to modify the code found at the link: http://stackoverflow.com/questions/17495644/using-vba-in-excel-to-google-search-in-ie-and-return-the-hyperlink-of-the-first?s=1|1.0033 — Rusty Roark, Nov 03 '14 at 15:51

score 1 · Answer 1 · answered Nov 03 '14 at 00:30

would not use the IE automation. From my point of view it's better to use the http request object instead:

you can do this by using a http request object:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "GET", "http://www.cboden.de"
oRequest.Send
MsgBox oRequest.ResponseText

If you are behind a proxy you can use something like this:

Const HTTPREQUEST_PROXYSETTING_PROXY = 2
Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.setProxy HTTPREQUEST_PROXYSETTING_PROXY, "http://proxy.intern:8080"
oRequest.Open "GET", "http://www.cboden.de"
oRequest.Send
MsgBox oRequest.ResponseText

and if you want to use POST (instead of the GET method) to pass some values to the webserver, you can try this:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "POST", "http://www.cboden.de/misc/posttest.php"
oRequest.SetRequestHeader "Content-Typ", "application/x-www-form-urlencoded"
oRequest.Send "var1=123&anothervar=test"
MsgBox oRequest.ResponseText

if you put it into a function then you can use it in you worksheet:

Function getCustomHyperlink(ByVal pURL As String) As String
    Dim oRequest As Object
    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", pURL
    oRequest.Send
    getCustomHyperlink = oRequest.ResponseText
End Function

within the worksheet you can then say for example:

=getCustomHyperlink("https://www.google.com/search?q=" & A1 )

if your search value is in A1

You can use that function also in another one where you exam the response with the normal text function like InStr, Left(), Right and so one. If you have find the first 10 URL's with that, you can use the function again to get their contents.

Using VBA in Excel to Google Search in IE and return the 5 or 10 results

1 Answers1