Word Hacks [Electronic resources] نسخه متنی

Hack 99 Use Google from Your Macros

This hack shows you how to access Google from
your Word macros.

With a bit of hacking, you can
use the ubiquitous Google search
engine without ever leaving Word. This hack shows you a simple macro
that lets you input a new search query, then displays the first 10
sites that Google says match your query.

10.11.1 Setting Up the Google Web Service

While Google's web site is a
model
of simplicity, it takes a bit of work to get to Google from your Word
macros. Here are the steps you'll need to take.

First, install the free Microsoft Office 2003 Web Services Toolkit
2.01. Search for it on the Microsoft web site (http://www.microsoft.com/downloads/) or
Google it.

Next, create a new template to hold your Google-related macros. The
Web Services Toolkit will create some code so you can work with
Google. A separate template will help you keep track of the code.
Create a new, blank document and save it as a document template named
GoogleTools.dot.

From your new GoogleTools.dot template, select
ToolsMacroVisual Basic Editor. The Web Services
Toolkit will have added a new item called Web Service References on
the Tools menu, as shown in Figure 10-16.

Figure 10-16. Creating a new reference for accessing Google

Select ToolsWeb Service References to display the dialog
shown in Figure 10-17. Enter
"google" in the Keywords field and
click the Search button. When the web service is found, check the box
next to it and click the Add button.

Figure 10-17. Locating the Google search web service

When you click the Add button, you'll notice a
flurry of activity on your screen as the Web Services Toolkit
installs several new class modules into your template project, as
shown in Figure 10-18.

Figure 10-18. The code created by the Web Services Toolkit

The Web Services Toolkit creates the code, but it actually comes from Google using WSDL (Web Services Description Language). The Toolkit interprets this information and generates the VBA code needed to access the web service (in this case, Google).

10.11.2 Getting a Google API Key

To access Google's Application
Programming Interface (API) from within your macros (or from any
program or script), you first need
a developer's
keya unique string assigned by Google to identify
you when you make queries from within a program. You can get more
information, along with a free developer's key, from
http://www.google.com/apis/.

For full-on coverage of the Google API, check out Google Hacks (O'Reilly).

Once you've signed up for a
developer's account, Google emails you a lengthy
string of characters like the following:

12BuCK13mY5h0E/34KN0cK@ttH3Do0R

In the macro code shown in this hack, replace
your_key_here with the actual key you got
from Google.

10.11.3 The Code

With your GoogleTools.dot template
open, go to the Visual Basic Editor,
choose GoogleTools in the Project Explorer, and then select
InsertModule to create a new code module.

Insert the following code in the new module:

Sub SimpleGoogleSearch( )
Dim vSearchResults As Variant
Dim v As Variant
Dim sResults As String
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
sGoogleAPIKey = "your_key_here"
lStart = 1
lMaxResults = 10
bFilter = True
sRestrict = "
bSafeSearch = False
sLanguageRestrict = "
sInputEncoding = "UTF-8"
sOutputEncoding = "UTF-8"
sSearchQuery = InputBox("Enter a Google query")
If Len(sSearchQuery) = 0 Then Exit Sub
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
On Error Resume Next
For Each v In vSearchResults
sResults = sResults & v.URL & vbCr
Next v
If Len(sResults) <> 0 Then
MsgBox "Found the following sites: " & vbCr & sResults
Else
MsgBox "Sorry, no results found"
End If
End Sub

Note the line:

Dim google_search As New clsws_GoogleSearchService

This line creates a new instance of one of the classes that the Web
Services Toolkit installed in your template.

The call to the google_search object is a bit
complex. Ten parameters are required for a Google API call. The
following list, adapted from Google Hacks,
describes the parameters:

str_key (key)

This is where you put your Google API developer's
key. Without a key, the query won't get very far.

str_q (query)

This is your query, composed of keywords, phrases, and special
syntaxes.

lng_start (start)

Also known as the offset, this value specifies at what result to
start counting when determining which 10 results to return. If this
number were 16, the Google API would return results 16-25; if 300, it
would return results 300-309 (assuming, of course, that your query
found that many results). This is what's known as a
"zero-based index"; counting starts
at 0, not 1. The first result is result 0, and the 999th is 998.
It's a little odd, admittedly, but you get used to
it quicklyespecially if you go on to do much programming.
Acceptable values are 0 to 999
because Google returns only up to a thousand results for a query.

lng_maxResults (maximum results)

This integer specifies the number of results you'd
like the API to return. The API returns results in batches of up to
10, so acceptable values are 1 through
10.

bln_filter (filter)

You might think this parameter concerns the SafeSearch filter for
adult content. It doesn't. This Boolean value
specifies whether your results go through automatic query filtering,
removing near-duplicate content (i.e., where titles and snippets are
very similar) and multiple (more than two) results from the same host
or site. With filtering enabled, only the first two results from each
host are included in the result set.

str_restrict (restrict)

No, this one doesn't have anything to do with
SafeSearch either. It allows for restricting your search to one of
Google's topical searches or to a specific country.
Google has four topic restricts: U.S. Government
(unclesam), Linux (linux),
Macintosh (mac), and FreeBSD
(bsd). You'll find the complete
country list in the Google Web API documentation. To leave your
search unrestricted, leave this option blank (usually signified by
empty quotation marks, ").

bln_safeSearch (safe search)

Here's the SafeSearch filtering option. This Boolean
specifies whether results returned will be filtered for questionable
(read: adult) content.

str_lr (language restrict)

This one's a bit tricky. Google has a list of
languages in its API documentation to which you can restrict search
results, or you can simply leave this option blank and have no
language restrictions.

There are several ways you can restrict to a language. First, you can
simply include a language code. If you wanted to restrict results to
English, for example, you'd use
lang_en. But you can also restrict results to more
than one language, separating each language code with a
| (pipe), signifying OR.
lang_en|lang_de, for example, constrains results
to only those in English or German.

You can omit languages from results by prepending them with a minus
sign (-). The phrase -lang_en
returns all results but those in English.

str_ie (input encoding)

This stands for "input encoding,"
allowing you to specify the character encoding used in the query
you're feeding the API. Google's
documentation says, "Clients should encode all
request data in UTF-8 and should expect results to be in
UTF-8." In the first iteration of
Google's API program, the Google API documentation
offered a table of encoding options (latin1,
cyrillic, etc.), but now everything is UTF-8. In
fact, requests for anything other than UTF-8 are summarily ignored.

str_oe (output encoding)

This stands for "output encoding."
As with input encoding, everything's UTF-8.

10.11.4 Running the Hack

To run the hack, close the Visual Basic Editor and return to Word.
Select ToolsMacroMacros, choose
SimpleGoogleSearch, and click the Run button.
You'll be prompted with the dialog box shown in
Figure 10-19

Figure 10-19. Doing a simple Google search from a macro

When you click the OK button, a new dialog appears, reporting the
URLs Google found to match your query (Figure 10-20).

Figure 10-20. The sites found by Google displayed in Word

This simple example will help you learn the Google API from Visual
Basic and get you ready for your own hacking projects. For a more
elaborate example, see [Hack #100] .