email article printer friendly

Connect Office Apps to XML Web Services
Add Web References to VBA code with the forthcoming Office XP Web Services Toolkit.
by Roger Jennings

The Colossus of Redmond, Big Blue, and latecomer Sun Microsystems are in a three-way race to dominate the Web services developer tools business. In a quest to add more distance to its current lead on the Java Web service crowd, Microsoft plans to release in January 2002 the Office XP Web Services Toolkit. This new Toolkit lets you add Visual Studio .NET-like Web References to Office XP's VBA-enabled members and automatically generates the class code you need to connect to and invoke the service.

The Office folks sent me a preview (RC0) of the Toolkit late in November. Setup adds an Office XP Web Services choice to your Programs menu, with Articles, Samples, and Help submenus. To run the sample apps for Microsoft Word, Outlook, or Access 2002, you need the SOAP Toolkit 2.0 or its runtime components; both are available from the MSDN Download Center. Some of the samples also require a reference to MSXML2 objects from the Microsoft XML v3.0+ library installed by IE 5.5+.

Most Web services are data-driven, so I used Access 2002's NorthwindCS.adp sample project to give the Toolkit a test run. The Toolkit adds a Web Services Reference item to the VBA editor's Tools menu, which opens a dialog of the same name (see Figure 1). The dialog isn't as fancy as the Visual Studio .NET version, but it performs the same functions. You can search the Microsoft UDDI registry for Web services by keyword or business name. For example, a search for services available from Mike Amundsen's EraServer site returns links to e-mail and postal address testing services. Select one of the operations and click on the Test button to open the ASP.NET description page of the service's .asmx file (see Figure 2).

Selecting a service and clicking on OK generates a clsws_ServiceName class. The Class_Initialize method creates an instance of the SOAP Toolkit's SoapClient object and invokes its mssoapinit method. Each Web service method generates a wsm_WSMethod(Parameters) proxy function derived from the service's Web Services Definition Language (WSDL) or .asmx file. Here's the code (with comments removed) for the clsws_ZipCodeResolver class:

Option Compare Database
Option Explicit
Private sc_ZipCodeResolver As SoapClient
Private Const c_WSDL_URL As String = 
"http://webservices.eraserver.net/
   zipcoderesolver/zipcoderesolver.asmx?wsdl"

Private Sub Class_Initialize()
   Set sc_ZipCodeResolver = New SoapClient
   sc_ZipCodeResolver.mssoapinit c_WSDL_URL
End Sub

Public Function wsm_FullZipCode(ByVal _
   str_accessCode As String, ByVal str_address As _
   String, ByVal str_city As String, ByVal str_state _
   As String) As String
   On Error GoTo wsm_FullZipCodeTrap
   wsm_FullZipCode = _
      sc_ZipCodeResolver.FullZipCode(str_accessCode,  _
      str_address, str_city, str_state)
Exit Function
wsm_FullZipCodeTrap:
   ZipCodeResolverErrorHandler "wsm_FullZipCode"
End Function

Public Function wsm_ShortZipCode(ByVal _
   str_accessCode As String, ByVal str_address As _
   String, ByVal str_city As String, ByVal str_state _
   As String) As String
   On Error GoTo wsm_ShortZipCodeTrap
   wsm_ShortZipCode = _
      sc_ZipCodeResolver.ShortZipCode(str_accessCode, _
      str_address, str_city, str_state)
Exit Function
wsm_ShortZipCodeTrap:
   ZipCodeResolverErrorHandler "wsm_ShortZipCode"
End Function

Public Function wsm_CorrectedAddressHtml(ByVal _
   str_accessCode As String, ByVal str_address As _
   String, ByVal str_city As String, ByVal str_state _
   As String) As String
   On Error GoTo wsm_CorrectedAddressHtmlTrap
   wsm_CorrectedAddressHtml = _
      sc_ZipCodeResolver.CorrectedAddressHtml(str_acces _
      sCode, str_address, str_city, str_state)
Exit Function
wsm_CorrectedAddressHtmlTrap:
   ZipCodeResolverErrorHandler _ 
      "wsm_CorrectedAddressHtml"
End Function

Public Function wsm_VersionInfo() As String
   On Error GoTo wsm_VersionInfoTrap
   wsm_VersionInfo = _
      sc_ZipCodeResolver.VersionInfo()
Exit Function
wsm_VersionInfoTrap:
   ZipCodeResolverErrorHandler "wsm_VersionInfo"
End Function

Private Sub _
   ZipCodeResolverErrorHandler(str_Function _
   As String)
   ' SOAP Error
   If sc_ZipCodeResolver.faultcode <> "" Then
      Err.Raise vbObjectError, str_Function, _
         sc_ZipCodeResolver.faultstring
   ' Non SOAP Error
   Else
      Err.Raise Err.Number, str_Function,  _
         Err.Description
   End If
End Sub

Class_TerminateTrap:
   ZipCodeResolverErrorHandler "Class_terminate"
End Sub

Private Sub Class_Terminate()
   'Error Trap
   On Error GoTo Class_TerminateTrap
   Set sc_ZipCodeResolver = Nothing
Exit Sub