Developing with Microsoft Word


I often use Microsoft Word to create reports based on Google data. A report could include a simple list of recommended Web sites, or it might provide detailed analysis of Web site usage. You could combine data from other Office sources, such as the chart example described in the "Defining Graphs and Charts" section of the chapter. Word helps you polish the data in a report to make it look and read better. It's also easier to perform text manipulation with Word. For example, you could download the current and the cached version of a Web page, compare the two, and create a report showing the differences.

Automating Reports Using SOAP

One of the reports that I find most useful is a comparison of the current and past states of Web pages. Sometimes a vendor will try to sneak a change into a page to overcome a poor choice of words. In other cases, it's very difficult to see what actually changes because the Web page originator fails to provide any indicators such as a new item icon. Fortunately, Word makes creating such a report almost too simple. You can download the cached document from Google Web Services, import it into a Word document, and then download the new document from the vendor's Web site. Using the Tools Merge Documents command with the Track Changes feature on shows precisely where the document has changed. Listing 5.6 shows how to download and import a cached document from Google. You'll find the complete source for this example in the \Chapter 05\Word folder of the source code located on the Sybex Web site.

Listing 5.6: Working with Cached Pages
start example
 Public Sub SimpleQuery()      Dim Client As SoapClient30 ' SOAP Client      Dim Doc() As Byte ' Entire Result Set      Dim ResultStr As String ' Converted string.      Dim Counter As Integer ' Loop counter.      ' Create and initialize the SOAP client.      Set Client = New SoapClient30      Client.MSSoapInit "http://api.google.com/GoogleSearch.wsdl", _                        "GoogleSearchService", _                        "GoogleSearchPort"      'Make a search request.      Doc = _         Client.doGetCachedPage( _            "Your-License-Key, _            "http://www.mwt.net/~jmueller/books/inprogre.html")      ' Convert the results to a string.      For Counter = 0 To UBound(Doc) - 1         ResultStr = ResultStr + Chr(Doc(Counter))      Next      ' Save the data as an HTML file.      Open ThisDocument.Path + "\Temp.HTM" For Output As #1      Write #1, ResultStr      Close #1      ' Send the data to screen.      Selection.InsertFile ThisDocument.Path + "\Temp.HTM"   End Sub 
end example
 

The code begins by creating a client. The client performs communication tasks with Google Web Services. In this case, the client uses the doGetCachedPage() method to obtain the cached page. Notice that VBA sees the returned information as a Byte array , which means you can't use the data directly.

After the code gets the document from Google Web Services, it uses a For loop to convert the Byte array to a String . The ResultStr variable contains the whole Web page. Unfortunately, if you pasted ResultStr into the document, the results would be unreadable. Word must convert the document from HTML to make it readable.

The easiest way to perform the document conversion is to save the document to disk as an HTML file. The code uses three simple steps to perform this task. While the Open, Write , and Close statements might seem like old technology, they work fast and efficiently for this task. The code then uses the Selection.InsertFile method to import the document from disk to the current Word pane. Figure 5.8 shows typical output from this example.

click to expand
Figure 5.8: You can easily download cached pages from Google for comparison purposes.
Note  

You can tell the Selection.InsertFile method to make an automatic conversion selection by setting the ConfirmConversions argument true. The problem with this approach is that Word doesn't always make the correct conversion choice. Letting the user ensure that Word selects HTML Document as the conversion choice ensures good application results.

Using the Web Service References Tool

The earlier examples in this chapter demonstrate that it's possible to create a reference to Google Web Services manually. However, there's an easier way to perform this task. You can use the Web Service References Tool for simple queries. A simple query is one that doesn't rely on complex data types for either the request or the response. For example, you could use this technique if your company already runs a Web service for mobile users that delivers a simple response given a simple request. You can also use it with Google Web Services, which is the method this section considers. (See the MSDN article entitled "Handling Complex SOAP Data Types in XML Web Services" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxpwst/html/odc_wsrtct.asp for details on the complex data issues with the Web Service References Tool.)

Warning  

Due to the limitations in earlier versions of the Web Service References Tool, I don't recommend this approach for earlier versions of Office. It works marginally with Office XP and Microsoft has promised better support for Office 2003.

To use this technique, you must have the Web Service References Tool loaded on your machine. Use the Add-Ins Add-Ins Manager command to display the Add-In Manager dialog box shown in Figure 5.9. Ensure the Web Service References add-in is both loaded and started, as shown in the figure.

click to expand
Figure 5.9: Use the Web Service References add-in to make using SOAP easier.
Note  

If you get an error message that VBA can't find the Web Service References Tool add-in on your machine, download the Microsoft Office XP Web Services Toolkit 2.0 at http://www.microsoft.com/downloads/details.aspx?FamilyId=4922060F-002A-4F5B-AF74-978F2CD6C798&displaylang=en. (You should also check for the 2003 version if you're using Office 2003, but the Office XP version seems to work.) Install the toolkit, open the Office XP Web Services Toolkit Overview, and then Web Services Reference Tool link on the Welcome page to install the tool.

The Web Services Reference Tool adds new menu entries to the VBA IDE. Once you know that the Web Service References add-in is running, you can use the following steps to create a reference to Google Web Services.

  1. Use the Tools Web Service References command to display the Web Service References Tool 2.0 dialog box.

  2. Select the Web Service URL option and type http://api.google.com/GoogleSearch.wsdl in the URL field.

  3. Click Search. After a few seconds (up to a minute), the Web Service References Tool 2.0 dialog box displays information for Google Web Services.

  4. Check the GoogleSearchService option and click OK. At this point, the VBA IDE will go a little crazy as VBA automatically creates the code required to access Google Web Services. You'd have to write this code yourself normally.




Mining Google Web Services
Mining Google Web Services: Building Applications with the Google API
ISBN: 0782143334
EAN: 2147483647
Year: 2004
Pages: 157

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net