O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  


 
Buy the book!
Word Hacks
By Andrew Savikas
November 2004
More Info

HACK
#100
Google Without Leaving Word
This hack shows you how to search Google from within Word and display the results on the Task Pane
The Code
[Discuss (0) | Link to this hack]

You probably use Google a few dozen times a day. If you work in Word, this means switching over to your web browser, checking the results, and then going back to Word. Once you get Google working from a macro , this hack will show how to display the search results in the New Document Task Pane .

This hack uses a configuration file to store data and some code that uses VBScript regular expressions . Before you dive in, check out those hacks.

Hacking the Hack

To take this hack one step further, you can modify it to use the currently selected text as the search text, rather than displaying an input box for you to enter text in.

The following macro, named GoogleSelectionToTaskPane, does a Google search of the currently selected text and displays the results in the Task Pane. The modified code is shown in bold.

Sub GoogleSelectionToTaskPane( )
Dim vSearchResults As Variant
Dim v As Variant
Dim sResults As String
Dim sEntryName As String
Dim sEntryURL As String
Dim sLogFile As String
Dim sSearchDisplayTitle As String
Dim sSearchURL As String
Dim i As Integer

' Google API variables
Dim sGoogleAPIKey As String
Dim sSearchQuery As String
Dim lStart As Long
Dim lMaxResults As Long
Dim bFilter As Boolean
Dim sRestrict As String
Dim bSafeSearch As Boolean
Dim sLanguageRestrict As String
Dim sInputEncoding As String
Dim sOutputEncoding As String
Dim google_search As New clsws_GoogleSearchService

' Initialize variables
sLogFile = "C:\google_taskpane.ini"
sGoogleAPIKey = your_key_here
lStart = 1
lMaxResults = 10
bFilter = True
sRestrict = ""
bSafeSearch = False
sLanguageRestrict = ""
sInputEncoding = "UTF-8"
sOutputEncoding = "UTF-8"

' Hide the Task Pane
Application.CommandBars("Task Pane").Visible = False

' Remove existing items from New Document Task Pane
For i = 0 To 9
    sEntryURL = System.PrivateProfileString( _
            FileName:=sLogFile, _
            Section:="GoogleTaskPane", _
            Key:="URLName" & CStr(i))
    sEntryName = System.PrivateProfileString( _
            FileName:=sLogFile, _
            Section:="GoogleTaskPane", _
            Key:="EntryName" & CStr(i))
    If Len(sEntryURL) > 0 Then
        Application.NewDocument.Remove _
            FileName:=sEntryURL, _
            Section:=msoBottomSection, _
            DisplayName:=sEntryName, _
            Action:=msoOpenFile
    End If
Next i

' Move ends of selection to exclude spaces
' and paragraph marks
Selection.MoveStartWhile cset:=Chr (32) & Chr (19), _
Count:=Selection.Characters.Count
Selection.MoveEndWhile cset:=Chr (32) & Chr (19), _
Count:=-Selection.Characters.Count

' Get selection text for searchsSearchQuery = Selection.Text
If Len(sSearchQuery) = 0 Then Exit Sub

' Get search results
vSearchResults = google_search.wsm_doGoogleSearch( _
        str_key:=sGoogleAPIKey, _
        str_q:=sSearchQuery, _
        lng_start:=lStart, _
        lng_maxResults:=lMaxResults, _
        bln_filter:=bFilter, _
        str_restrict:=sRestrict, _
        bln_safeSearch:=bSafeSearch, _
        str_lr:=sLanguageRestrict, _
        str_ie:=sInputEncoding, _
        str_oe:=sOutputEncoding).resultElements

' Check for no results
On Error Resume Next
v = UBound(vSearchResults)
If Err.Number = 9 Then
    MsgBox "No results found"
    Exit Sub
ElseIf Err.Number <> 0 Then
    MsgBox "An error has occurred: " & _
        Err.Number & vbCr & _
        Err.Description
    Exit Sub
End If

' Add each result to the Task Pane
' and to the log file
i = 0
For Each v In vSearchResults
    sSearchURL = v.URL
    sSearchDisplayTitle = StripHTML(v.title)
    Application.NewDocument.Add _
        FileName:=sSearchURL, _
        Section:=msoBottomSection, _
        DisplayName:=sSearchDisplayTitle, _
        Action:=msoOpenFile
        
    System.PrivateProfileString( _
        FileName:=sLogFile, _
        Section:="GoogleTaskPane", _
        Key:="URLName" & CStr(i)) = sSearchURL
    System.PrivateProfileString( _
        FileName:=sLogFile, _
        Section:="GoogleTaskPane", _
        Key:="EntryName" & CStr(i)) = _
            sSearchDisplayTitle
    i = i + 1
Next v

' Show the New Document Task Pane
CommandBars("Menu Bar").Controls("File").Controls("New...").Execute

End Sub

To help ensure a good Google search, the following two lines collapse two ends of the selection if they contain spaces or a paragraph mark:

Selection.MoveStartWhile cset:=Chr (32) & Chr (19), _
    Count:=Selection.Characters.Count
Selection.MoveEndWhile cset:=Chr (32) & Chr (19), _
    Count:=-Selection.Characters.Count

Add the GoogleSelectionToTaskPane macro to your Text shortcut menu , and Google results will be just a right-click away.

The Code

Open the GoogleTools.dot template you created when you installed the Web Services Toolkit, as discussed in . Select Tools→Macro→Macros and insert the following code, which consists of a procedure named GoogleToTaskPane and a supporting function named StripHTML.

Sub GoogleToTaskPane( )
Dim vSearchResults As Variant
Dim v As Variant
Dim sResults As String
Dim sEntryName As String
Dim sEntryURL As String
Dim sLogFile As String
Dim sSearchDisplayTitle As String
Dim sSearchURL As String
Dim i As Integer

' Google API variables
Dim sGoogleAPIKey As String
Dim sSearchQuery As String
Dim lStart As Long
Dim lMaxResults As Long
Dim bFilter As Boolean
Dim sRestrict As String
Dim bSafeSearch As Boolean
Dim sLanguageRestrict As String
Dim sInputEncoding As String
Dim sOutputEncoding As String
Dim google_search As New clsws_GoogleSearchService

' Initialize variables
sLogFile = "C:\google_taskpane.ini"
sGoogleAPIKey = "your_key_here"
lStart = 1
lMaxResults = 10
bFilter = True
sRestrict = ""
bSafeSearch = False
sLanguageRestrict = ""
sInputEncoding = "UTF-8"
sOutputEncoding = "UTF-8"

' Hide the Task Pane
Application.CommandBars("Task Pane").Visible = False

' Remove existing items from New Document Task Pane
For i = 0 To 9
    sEntryURL = System.PrivateProfileString( _
            FileName:=sLogFile, _
            Section:="GoogleTaskPane", _
            Key:="URLName" & CStr(i))
    sEntryName = System.PrivateProfileString( _
            FileName:=sLogFile, _
            Section:="GoogleTaskPane", _
            Key:="EntryName" & CStr(i))
    If Len(sEntryURL) > 0 Then
        Application.NewDocument.Remove _
            FileName:=sEntryURL, _
            Section:=msoBottomSection, _
            DisplayName:=sEntryName, _
            Action:=msoOpenFile
    End If
Next i

' Get new search query
sSearchQuery = InputBox("Enter a Google query:")
If Len(sSearchQuery) = 0 Then Exit Sub

' Get search results
vSearchResults = google_search.wsm_doGoogleSearch( _
        str_key:=sGoogleAPIKey, _
        str_q:=sSearchQuery, _
        lng_start:=lStart, _
        lng_maxResults:=lMaxResults, _
        bln_filter:=bFilter, _
        str_restrict:=sRestrict, _
        bln_safeSearch:=bSafeSearch, _
        str_lr:=sLanguageRestrict, _
        str_ie:=sInputEncoding, _
        str_oe:=sOutputEncoding).resultElements

' Check for no results
On Error Resume Next
v = UBound(vSearchResults)
If Err.Number = 9 Then
    MsgBox "No results found"
    Exit Sub
ElseIf Err.Number <> 0 Then
    MsgBox "An error has occurred: " & _
        Err.Number & vbCr & _
        Err.Description
    Exit Sub
End If

' Add each result to the Task Pane
' and to the log file
i = 0
For Each v In vSearchResults
    sSearchURL = v.URL
    sSearchDisplayTitle = StripHTML(v.title)
    Application.NewDocument.Add _
        FileName:=sSearchURL, _
        Section:=msoBottomSection, _
        DisplayName:=sSearchDisplayTitle, _
        Action:=msoOpenFile
        
    System.PrivateProfileString( _
        FileName:=sLogFile, _
        Section:="GoogleTaskPane", _
        Key:="URLName" & CStr(i)) = sSearchURL
    System.PrivateProfileString( _
        FileName:=sLogFile, _
        Section:="GoogleTaskPane", _
        Key:="EntryName" & CStr(i)) = sSearchDisplayTitle
    i = i + 1
Next v

' Show the New Document Task Pane
CommandBars("Menu Bar").Controls("File").Controls("New...").Execute

End Sub

Function StripHTML(str As String) As String
Dim re As Object
Dim k As Long
On Error Resume Next
Set re = GetObject(Class:="VBScript.RegExp")
If Err.Number = 429 Then
    Set re = CreateObject(Class:="VBScript.RegExp")
    Err.Clear
ElseIf Err.Number <> 0 Then
    MsgBox Err.Number & vbCr & Err.Description
End If

' Check for common character entities by ASCII value
For k = 33 To 255
    re.Pattern = "&#" & k & ";"
    str = re.Replace(str, Chr$(k))
Next k

' Remove common HTML tags
re.Pattern = "<[^>]+?>|&[^;]+?;"
re.Global = True
str = re.Replace(str, vbNullString)
StripHTML = str
End Function

TIP

Make sure you replace the value your_key_here with your Google developer's key.

This hack uses two parts of the Google search results: the URLs and titles. Google formats the search result title as HTML, but you can put only plain text in the Task Pane. The StripHTML function uses a few simple VBScript regular expressions to strip out common HTML tags (such as <b>) and replace character entities (such as &#64;) with their ASCII character equivalents ( ).

TIP

The StripHTML function uses late binding, as discussed in .

It can be tricky to remove files from the Task Pane using VBA unless you know their exact names, as discussed in . This macro, however, stores the search results in a .ini file. Thus, the next time you do a search, you can easily remove the previous results. The macro uses a file named C:\google_taskpane.ini, which is defined in the GoogleToTaskPane procedure.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.