Calling XML Web Services from Office VBA Using the Web Service References Tool

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Paul Cornell
Microsoft Corporation

January 2002

Applies to:
Microsoft® Office XP

Summary: The Web Service References Tool is used in the Microsoft Office Visual Basic Editor to create a Visual Basic for Applications (VBA) proxy class module from a selected Web Service Description Language (.wsdl) or Visual Studio .NET discovery (.vsdisco) file. This article describes how to use the Web Service References Tool to add programmatic flexibility and power to your Microsoft Office solutions. (11 printed pages)

Download Office XP Web Services Toolkit.

Introduction
What Is the VBA Web Service References Tool? Using the Web Service References Tool
Code Walkthrough: Examining the Proxy Class Code
Troubleshooting Conclusion

Introduction

In traditional Microsoft Office Visual Basic® for Applications (VBA) solutions development, programmers provide application logic in the form of code modules or Component Object Model (COM) add-ins. Generally, these code modules and add-ins must physically reside on client computers. For example, if you want to distribute an Office VBA macro or COM add-in, you would need to install the macro or add-in on each client computer that you wanted to use the code.

Developers now have access to an ever-growing library of programmable application logic on the Internet or a corporate intranet (provided, of course, that the appropriate access rights have been granted) through a technology called XML Web services. You can think of XML Web services much like macros or add-ins that are available over the Web. Provided a connection exists between a client computer and a computer hosting the XML Web service, the XML Web service can be consumed by an application running on the client computer without any additional software (other than the Web Service References Tool, the SOAP Toolkit 2.0 SP2 Redistributable Files, or Microsoft Windows® XP).

To shorten development cycles, Universal Description, Discovery, and Integration (UDDI) is another recent technology that allows you to quickly, easily, and dynamically find and program against registered XML Web services.

This article describes how the Web Service References Tool provides an interface for discovering XML Web services, allows you to select the XML Web services you want, and creates proxy classes for your Office Visual Basic for Applications (VBA) project that reference those XML Web services. The tool is accessible from the Web Service References command on the Tools menu in the Office Visual Basic Editor once the Web Service References Tool is installed.

For more information about XML Web services and UDDI, see the MSDN Web Services Developer Center. To see sample XML Web services in action, try out the .NET Sample Web Services.

What Is the VBA Web Service References Tool?

Without the Web Service References Tool, you must go through a series of complicated and error-prone steps to call an XML Web service from an Office solution. First, you must know the URL to the XML Web service's Web Service Description Language (WSDL) file, along with the XML Web service's method names and input and output parameters (this information is typically gathered by contacting the XML Web service's administrator). Furthermore, because early binding cannot be used to call XML Web services in Office VBA, you do not get the benefit of IntelliSense®, and syntax checking against XML Web services does not occur until run time.

The Web Service References Tool makes integrating XML Web services into Office VBA solutions simpler by providing a user interface for making the finding and selecting of XML Web services easier. Once an XML Web service is selected, the tool creates a VBA proxy class encapsulating all of the XML Web service's publicly exposed methods and input and output parameters. You can then declare and instantiate objects based on these classes, just as you would for any other type of intrinsic Office objects, and you get the added benefit of IntelliSense and syntax checking against XML Web services at design time.

Using the Web Service References Tool

All that is required on the development computer is the Web Service References Tool and Microsoft Office XP. On a user’s computer, the minimum installation requirements include one of the following:

The Web Service References Tool
The SOAP Toolkit 2.0 SP2 Redistributable Files
Microsoft Windows XP

After the Web Service References Tool is installed on the development computer, open the Office Visual Basic Editor in any Office application that supports Visual Basic for Applications (for example, Microsoft Access, Microsoft Excel, Microsoft FrontPage®, Microsoft Outlook®, Microsoft PowerPoint®, Microsoft Visio®, Microsoft Word, and so on). Set a reference to the Microsoft Soap Type Library (MSSOAP1.DLL) from the References dialog box (Tools menu). Then, on the Tools menu, click Web Service References.

**Note **If you are calling XML Web services on the Internet from behind your organization's Internet firewall or through an Internet proxy service, contact your network administrator for additional installation and configuration details.

To run your solution on the client side, users must have not only your solution code and Microsoft Office XP, but also the Web Service References Tool, the SOAP Toolkit 2.0 Redistributable Files, or Microsoft Windows XP.

The Web Service References Tool is simple to use (see Figure 1).

Aa140260.odc_ofwrst01(en-us,office.10).gif

Figure 1. The Web Service References Tool.

Some of the important items in the Web Service References Tool include:

The Web Service Search area, which allows you to perform an XML Web service search using keywords or a business name against a UDDI Business Registry.

**Note **In many cases, searching for an XML Web service using keywords or a business name will return the desired results. However, if you want to search using whole words, perform a case-sensitive search, or if you know that the XML Web service you are looking for resides on a different UDDI server than http://uddi.microsoft.com/inquire, you can specify your UDDI search criteria by clicking the More button.

The Web Service URL area, which allows you to perform an XML Web service search using a specific URL to a WSDL or Visual Studio .NET discovery (.vsdisco) file. For information about using wildcards, see Help in the Web Service References Tool.

**Tip **If the XML Web service administrator does not provide a WSDL file but does provide an .asmx file, you may be able to append the string ?wsdl to the end of the URL containing an .asmx file. For example, you can try http://example.microsoft.com/XMLWebServiceFileName.asmx?wsdl if http://example.microsoft.com/XMLWebServiceFileName.wsdl doesn't work.

**Tip **If the XML Web service administrator has enabled discovery of XML Web services at a given URL, you can type in the URL to the Visual Studio .NET discovery (.vsdisco) file. The .vsdisco file contains a list of all available XML Web services at a given URL. For example, if a discovery file exists at http://example.microsoft.com/*DiscoveryFileName*.vsdisco, a list of available XML Web services will be returned.

The Search Results area, which allows you to select available XML Web services for which you want the tool to create proxy classes.
The Test Method button, which allows you to test an XML Web method you select in the Search Results area (if the XML Web service administrator has provided a corresponding .asmx test harness page).

Once you select one or more XML Web services for which you want the tool to create proxy classes and click the Add To Project button, the Web Service References Tool will:

Create one class module (.cls) file for each selected XML Web service. Each .cls file will begin with the prefix clsws_ followed by the XML Web service's name. For instance, if I add an XML Web service to my project with the name FlightStatus, the class name will be clsws_FlightStatus.
Create one Private object variable in the .cls file that corresponds to an instance of the XML Web service. This object variable will begin with the prefix sc_ followed by the XML Web service's name. Continuing with the example above, this object variable will be named sc_FlightStatus.
Create one Private constant in the .cls file that corresponds to the XML Web service's WSDL URL. This constant is always named c_WSDL_URL.
Create one Public function in the .cls file for each exposed Web method in the XML Web service. The function always begins with wsm_ followed by the Web method's name and corresponding input and output parameters (since parameters are sent and received using SOAP, the Web Service References Tool must translate SOAP data types to VBA data types). For example, if the XML Web service exposes a Load Web method with input parameters flightnumber of SOAP data type xsd:short, flightdate of SOAP data type xsd:dateTime, and accessCode of SOAP data type xsd:string, and returns a complex SOAP data type of xsd:string, the tool converts this to:

Public Function wsm_Load(ByVal int_flightNumber As Integer, _
    ByVal dtm_flightDate As Date, ByVal str_accessCode As String) _
    As String

To code against this example .cls file, use code like this:

...
Dim objFlightStatus As clsws_FlightStatus
Dim strResult As String
    
Set objFlightStatus = New clsws_FlightStatus
    
strResults = objFlightStatus.wsm_Load _
    (int_flightNumber:=123, _
    dtm_flightDate:=#1/1/2002#, _
    str_accessCode:="ABC-123")
...

Notice that as you code, you get the benefits of early binding and IntelliSense.

Code Walkthrough: Examining the Proxy Class Code

Let's examine in greater detail the proxy class code that is generated by the Web Service References Tool. For this example, I used the Eight Ball XML Web service from GotDotNet.com. To try this out, in the Web Service References Tool:

Type gotdotnet in the Business Name box and click Search.
In the Search Results pane, check the EightBallWS box and click Add To Project.

The tool generates the class module titled clsws_EightBallWS containing the code listed below. The first section provides comments about how this code was created, followed by instructions on how to declare and instantiate this class in code. Class modules generated by the tool always start with the prefix clsws_ followed by the XML Web service name.

Option Explicit
'*****************************************************************
' This class was created by the Web Service References Tool.
'
' Created: <date> <time>
'
' Description:
' This class is a Visual Basic for Applications class representation 
' of the Web service EightBallWS
' as defined by 
' http://www.gotdotnet.com/playground/services/EightBall/eightballws.asmx?wsdl
'
' This class only contains methods that use simple data types,
' as defined in the WSDL.
'
' To Use:
' Dimension a variable as new clsws_EightBallWS, and then write code to
' use the methods provided by the class.
'
' Example:
'   Dim ExampVar as New clsws_EightBallWS
'   debug.print ExampVar.wsm_Ask("Sample Input")
'
' Changes to this code may result in incorrect behavior.
'
'*****************************************************************

Next, two internal class variables are dimensioned, representing the SoapClient proxy object and the URL to the XML Web service's WSDL file, respectively. The SoapClient proxy object always starts with the prefix sc_ followed by the XML Web service name. The constant c_WSDL_URL will always have the same name regardless of the class file.

' Dimensioning private class variables.
Private sc_EightBallWS As SoapClient
Private Const c_WSDL_URL As String = _
    "http://www.gotdotnet.com/playground/services/EightBall/eightballws.asmx?wsdl"

The Class_Intialize event is used to instantiate the SoapClient proxy object. This proxy object is used to pass XML Web service calls between the client code and the Web server.

Private Sub Class_Initialize()
'*************************************************************
' Subroutine will be called each time the class is instantiated.
' Creates sc_EightBallWS as new SoapClient, and then
' initializes sc_EightBallWS.mssoapinit with WSDL file found in
' http://www.gotdotnet.com/playground/services/EightBall/eightballws.asmx?wsdl.
'*************************************************************

    Set sc_EightBallWS = New SoapClient
    sc_EightBallWS.mssoapinit c_WSDL_URL
 
End Sub

The Class_Terminate event is used to clean up resources that are no longer needed, namely the SoapClient proxy object.

Private Sub Class_Terminate()
'*************************************************************
' Subroutine will be called each time the class is destructed.
' Sets sc_EightBallWS to Nothing.
'*************************************************************
 
    'Error Trap
    On Error GoTo Class_TerminateTrap
 
    Set sc_EightBallWS = Nothing
 
Exit Sub
 
Class_TerminateTrap:
    EightBallWSErrorHandler ("Class_terminate")

End Sub

The EightBallWSErrorHandler subroutine is used to handle any errors raised by the Web server hosting the Web service. This subroutine's name is always prefixed by the XML Web service name followed by the text ErrorHandler.

Private Sub EightBallWSErrorHandler(str_Function As String)
'*****************************************************************
' This subroutine is the class error handler. It can be called from any
' class subroutine or function when that subroutine or function
' encounters an error. Then, it will raise the error along with the
' name of the calling subroutine or function
'
'*****************************************************************
 
    ' SOAP Error
    If sc_EightBallWS.faultcode <> "" Then
        Err.Raise vbObjectError, str_Function, _
            sc_EightBallWS.faultstring
    ' Non SOAP Error
    Else
        Err.Raise Err.Number, str_Function, Err.Description
    End If
 
End Sub

Finally, the XML Web service's methods are declared. In this case, there is only one Web method, Ask. Web method signatures created by the Web Service References Tool:

Are always declared as Public Function.
Are always prefixed by the text wsm_ followed by the Web method name.
List input parameters with a data type prefix followed by the Web method parameter.
Here is the generated code:

Public Function wsm_Ask(ByVal str_psQuestion As String) As String
'*************************************************************
' Proxy function created from
' http://www.gotdotnet.com/playground/services/EightBall/eightballws.asmx?wsdl
'*************************************************************
 
    'Set error trap
    On Error GoTo wsm_AskTrap

    wsm_Ask = sc_EightBallWS.Ask(str_psQuestion)

Exit Function

wsm_AskTrap:
    EightBallWSErrorHandler wsm_Ask

End Function

So now, if you write the following code to consume the XML Web service:

Public Sub TestEightBallAskWebMethod()

    Dim objEightBall As clsws_EightBallWS
    Set objEightBall = New clsws_EightBallWS
    
    MsgBox "Answer: " & objEightBall.wsm_Ask _
        (str_psQuestion:="Do I feel lucky today?")
    
End Sub

You get the results shown in Figure 2.

Aa140260.odc_ofwsrt02(en-us,office.10).gif

Figure 2. The answer to "Do I feel lucky today?"

Troubleshooting

The five common problems you will encounter when calling XML Web services include:

Problem #1: The user's computer does not have access to the Internet.

Possible causes: The user's computer is offline; network administrators have blocked individual users' computers from accessing the Internet.
Possible solutions/workarounds: Make sure the user's computer has connectivity to the Internet; check with your network administrator regarding your organization's Internet access policies.

Problem #2: The user's computer does not have the appropriate software installed.

Possible causes: The Microsoft Soap Type Library (mssoap1.dll) is not present or is not registered.
Possible solutions/workarounds: Make sure the user's computer is running Windows XP or has the SOAP Toolkit 2.0 SP2 Redistributable Files or the Web Service References Tool installed; check the user's computer for the location of the Microsoft Soap Type Library (mssoap1.dll) and reregister it if necessary; make sure the user has all of the appropriate solution files, including any VBA proxy class modules generated by the Web Service References Tool.

Problem #3: The XML Web service is not available, and you receive a "the page cannot be displayed" error when you type in the URL to the XML Web service's WSDL file in Microsoft Internet Explorer.

Possible causes: The XML Web service provider has removed or relocated the XML Web service from the path referenced in the VBA proxy class module generated by the Web Service References tool.
Possible solutions/workarounds: Contact the XML Web service provider for the new URL of the XML Web service's WSDL file.

Problem #4: A syntax error is displayed when calling the XML Web service.

Possible causes: The XML Web service provider has changed the XML Web service's input or output parameters.
Possible solutions/workarounds: Consult the original WSDL or .asmx file and check the XML Web service's input and output parameters.

Problem #5: Internet traffic is heavy, which slows or blocks access to an otherwise available XML Web service.

Possible causes: Various reasons, most of which are usually outside of your control.
Possible solutions/workarounds: Try calling the XML Web service again later.

Conclusion

The Web Service References Tool opens up an ever-growing source of programmable application logic over the Internet and corporate intranets. By using the Office Visual Basic Editor, programmers can now set references to XML Web services just as they would any intrinsic type library. The VBA proxy classes generated by the tool allow early binding and IntelliSense, which makes consuming XML Web services easier and more intuitive, and virtually eliminates syntax errors.