02 May 2002

Thu, 02 May 2002

GoogleLookup Macro - MS Office VBA & SOAP

I've been talking for a while about how cool it would be if you could just load all of the top google searches simultaneously in the browser. At the same time, I've been thinking about exactly how to accomplish that. I decided tonight that an interesting way to do this would be to hack together a VBA macro that would take whatever text selection that you made in a Microsoft Office application, say Word, for instance, and do the google search and launch all of the results in the browser automatically. This works very nicely in concert with a browser like Opera that uses an MDI parent window with each page in a child window with a tab associated with each child window. With IE, you wind up with a lot of windows. Anyhow, the code's below.


This builds on my original VBScript posting which was hacked by the fine folks at WebJives. The code gets a bit more thorny because of the parse needed to retrieve the URL. Other than that, it's pretty straightforward. I hope this helps to demonstrate what's possible with a little scripting code and the Google SOAP API. It was fun certainly fun to build.


Installation Instructions



  1. This has been tested on Windows XP and Windows NT running Word XP and Word 2000. YMMV. Thanks to Deepak at WebJives for additional testing and bug corrections. You'll need to have a version of Windows that supports SOAP out of the box like Windows XP or you'll have to grab the MS SOAP Toolkit here. There shouldn't be any funky dependencies with the exception of a recent version of Microsoft Office.
  2. Copy the code to the clipboard.
  3. Start Microsoft Word.
  4. Click Tools/Macro/Macros on the menu bar.
  5. Type a name for the macro and click the create button (I used the name GoogleLookup below).
  6. Past the code into the VBA macro editor while taking care that the name of the sub matches what you entered in step 4.
  7. Find the line that says key="YourKeyGoesHere" and paste your google key into the quotes. You can get a google key here if you don't have one.
  8. If you use a proxy server, uncomment the three lines to configure the proxy and set the values appropriately.
  9. Save and exit the VBA macro editor.
  10. In your document, type some text, highlight a word or two, then click Tools/Macro/Macros from the menu bar, select the macro that you just created, and click the run button. Your browser should launch with the search results displayed for the terms that you highlighted in the document.
  11. Questions, comments, etc.  Click here to send an email to the editor of this weblog. I'd be curious to hear of results with various Windows variants, Office applications, and/or browsers.

Enjoy!


Sub GoogleLookup()
    '
    ' GoogleLookup Macro
    ' Macro created 5/2/2002 by David Watson
    '
    Dim soapClient, Results, i, key, query, ResultElements, ResultCount
    Set soapClient = CreateObject("MSSOAP.SoapClient")
    key = "YourGoogleKeyGoesHere"
    On Error Resume Next
    Rem If you are connecting through a proxy server, Keep the following three lines
    Rem soapClient.ClientProperty("ServerHTTPRequest") = True
    Rem soapClient.ConnectorProperty("ProxyServer") = "your.proxy.com"
    Rem soapClient.ConnectorProperty("ProxyPort") = 8080
    soapClient.mssoapinit ("http://api.google.com/GoogleSearch.wsdl")
    If Err Then
        MsgBox Err.Description
        MsgBox "Faultstring =" + soapClient.faultString
        MsgBox "Faultactor =" + soapClient.faultactor
        MsgBox "Faultcode =" + soapClient.faultcode
        MsgBox "Detail =" + soapClient.detail
    End If
    Set Results = soapClient.doGoogleSearch(key, Selection, 0, 10, False, "", False, "", "", "")
    If Err Then
        MsgBox Err.Description
        MsgBox "Faultstring =" + soapClient.faultString
        MsgBox "Faultactor =" + soapClient.faultactor
        MsgBox "Faultcode =" + soapClient.faultcode
        MsgBox "Detail =" + soapClient.detail
    Else
        For i = 0 To Results.Length - 1
            If Results.Item(i).nodeName = "resultElements" Then
                First = 0
                Last = 1
                For j = 1 To 10
                    First = InStr(Mid(Results.Item(i).nodeTypedValue, 1), "http://")
                    Last = InStr(Mid(Results.Item(i).nodeTypedValue, First), Chr(10))
                    tempstr = Mid(Results.Item(i).nodeTypedValue, First, Last)
                    If Not (tempstr = "") And Not (First = 0) And Not (Last = 0) Then
                        ActiveDocument.FollowHyperlink (tempstr)
                        Results.Item(i).nodeTypedValue = Mid(Results.Item(i).nodeTypedValue, First + Last)
                    Else
                        Exit For
                    End If
                Next
                Exit For
            End If
        Next
    End If
End Sub

Posted at: 23:13 | permalink