Office XP
New Toolkit Lets You Share Information Between Office Documents and Web Services
Krishnamurthy Srinivasan
Code download available at:OfficeXPToolkit.exe(68 KB)
This article assumes you're familiar with SOAP, WSDL, UDDI, and Visual Basic
Level of Difficulty123
SUMMARY
The Office XP Web Services Toolkit makes it possible to build applications that gather information and trigger transactions through various Web Services. The toolkit allows you to easily discover Web Services remotely. It also includes the Web Service Reference Tool, which lets you call a Web Service from inside an Office application. This article shows how toolkit-generated code can be used to access simple, as well as complex, Web Services.
The author steps through the auto-generated code to explain the classes that collect parameters, the schema to format the request/response, and the actual operations of the Web Service.
Contents
What's in the Toolkit?
Business Scenario
RPC-oriented Web Service
Registering the Web Service in UDDI
Developing an Excel Client
Document-style Web Services
The ASP.NET Document-style Web Service
Developing the Excel Client for the New Service
Wrapping Up the Excel Transportation App
Conclusion
M icrosoft® Office has been a favorite tool for enhancing the personal productivity of knowledge workers, such as financial analysts and production planners, due to the unique capabilities it offers for organizing and analyzing information. But to use Office applications to solve a problem, you need to get all your data into a single Office document. With the introduction of Office XP came the ability to create powerful, custom information hub applications that integrate information from the enterprise and the Internet. Office XP can automate the integration of information found in a variety of formats including HTML and XML. Also, the Office XP Web Services Toolkit minimizes the programming required to integrate data returned from Web Services.
Office XP and Web Services together offer a very compelling platform for building solutions to manage collaborative planning applications that manage supply chains, as well as any application that needs access to heterogeneous environments.
What's in the Toolkit?
The Office XP Web Services Toolkit allows you to use a GUI to either search for Web Services from any UDDI registry or specify a particular Web Service by providing a URL to the file that contains the Web Service description. The service description file can be in Web Services Description Language (WSDL) format, and the Visual Studio® Discovery (VSDisco) format is still supported even though it's obsolete. In case the description file contains information on multiple Web Services, the GUI allows the user to select either some or all of the Web Services for use within their current document. The tool then generates the code for classes that access the Web Services using the Microsoft SOAP client object. These generated classes will have methods corresponding to the operations supported by the Web Services. The user can write simple Visual Basic® for Applications (VBA) scripts without having to know that these are really Web Services.
The Office XP Web Services Toolkit includes the Microsoft SOAP Toolkit 3.0 as well as MSXML 4.0. The Office XP Web Services Toolkit also includes the Web Services References Tool, which allows you to locate Web Services.
Business Scenario
As I said before, the Office XP Web Services Toolkit is a great tool for solving supply chain management problems. To illustrate this point, I will consider a company with multiple manufacturing plant locations and distribution centers. I know the goods available at each plant and the items required at each distribution center. I can obtain the cost of shipping between any plant and any distribution center from many shipping companies using their Web Services. In this case, I'll always choose the shipping company that quotes the lowest rate. Now, I need to determine how much to ship from each plant to each distribution center to minimize the total shipping cost. Figure 1 shows the Excel spreadsheet that I'll use to solve this transportation problem.
Figure 1** Using an Excel Spreadsheet for the Transportation App **
The yellow cells on the lower section of the spreadsheet show the transportation costs. I arrived at these numbers by obtaining the cost from multiple shipping companies using their Web Services and identifying the lowest bid. The top half of the spreadsheet shows the quantity to be shipped from each plant to each distribution center once I solve the allocation problem. This number will be determined by the Excel Solver, a scenario (what if) simulator included with Excel.
I will take a brief look at the simple Web Service that provides shipping rate quotes using simple data types as the input and output parameters and using the RPC-oriented SOAP communication. Then I will use the Web Services References Tool included in the toolkit to discover this service from UDDI and communicate with the service, all without leaving the spreadsheet. Finally, I will look at a Web Service that uses XML documents conforming to a standard schema as input and output parameters, paying particular attention to how the toolkit handles such Web Services with complex parameters.
RPC-oriented Web Service
I am working with a Web Service built using ASP.NET; however, the Web Services References Tool does not make any assumptions about the implementation tool or platform—its only requirement is that the Web Service have a valid WSDL or VSDisco file to describe it. The following code outlines the .asmx file that contains a Web Service for providing shipping quotes:
<%@ WebService Language="VB" Class="ShippingRateQuote"%> Imports System Imports System.Web.Services <WebService(Namespace:="https://ksriniv3-mobl/ShippingRateQuote")> _ Public Class ShippingRateQuote : _ Inherits WebService <WebMethod()> Public Function _ GetRate(city1 as string, city2 _ as string) as Integer //Deleted the code for providing a //shipping rate quote End Function End Class
This simple service takes two city names as input and returns the rate for shipping between them. See XML Web Services Basics for a further discussion of the ASP.NET syntax.
Once you deploy the .asmx file on the Web server, the prospective users can obtain the WSDL description of the service by appending "?WSDL" to the URL for the .asmx file. In the resulting WSDL file, the name of my service will be the same as that of my class, ShippingRateQuote, which, as the method name suggests, will obtain the shipping rate.
Registering the Web Service in UDDI
The best practice for registering a Web Service with a WSDL descriptor in UDDI is described in Using WSDL in a UDDI Registry. In this case, I am going to register the ShippingRateQuote service under a shipping market called Ship Yonder. Several other shipping companies could also register their Web Services under this shipping market. I will not go into the details of registering a Web Service in UDDI here, but I essentially created a tModel on the Microsoft test UDDI site based on the WSDL for my Web Service, created a business called Ship Yonder, registered my ShippingRateQuote as a service offered by this business, and created a binding between this service and the tModel.
Developing an Excel Client
I'll start with a spreadsheet like the one in Figure 1. If you download the code with this article, you can use the file named inventory assignment pre ws.xls (see the link at the top of this article). Press Alt-F11 to get to the Visual Basic editor. From there you can invoke the toolkit by going to Tools | Web Service References. The resulting window is shown in Figure 2.
Figure 2** Web Service References **
The left-hand side of the window receives search parameters including the UDDI server or Web Service URL. The references tool searches in the Microsoft production UDDI registry at https://uddi.microsoft.com by default. To specify another UDDI registry (the Microsoft test server in this case), click the More/Less button on the top-left panel, then change the server location in the UDDI Server field (see Figure 2). Valid entries include the Microsoft test registry, your own private registry, or any other public UDDI registry. You can search for Web Services using the business names (as I have done) or keywords. By default, services are returned even if only a part of the words that are specified match the entry in the UDDI registry, and the search is not case sensitive.
The references tool does not flood you with results as does the search through the Microsoft UDDI Web page (or the Web interface to any other public registry, for that matter) since it shows only those services that provide at least one service that has a WSDL description (see Figure 3). For example, if I use "ship%" on the Web page, I get a list of many organizations that have "leadership" and "scholarship" in their name. Try that with the toolkit and see what you get back!
Figure 3** Shipping Rate Quote **
The search I mentioned yields two Ship Yonder services: ShippingRateQuote and ShippingRateQuote2. Let's consider ShippingRateQuote. You can expand the service to see the list of methods it supports by clicking the plus sign (see Figure 3). Select the first service and then click the Add button.
This results in the generation of a new class module, its addition to my VBA project, as well as the addition of references to the Microsoft SOAP Type Library and MSXML 3.0. The class is named by appending the prefix "clws_" to the name of the service in the WSDL file (see Figure 4).
Figure 4 Newly Generated Class Module
''***************************************************************** 'This class was created by the Web Service References Tool 2.0. ' 'Description: 'This class is a Visual Basic for Applications class representation of the 'Web Service as defined by https://ksriniv3-mobl/shipyonder/ 'shippingChargesQuote.asmx?WSDL. ' ' Comments deleted. ' '***************************************************************** 'Dimensioning private class variables. Private sc_ShippingRateQuote As SoapClient30 Private Const c_WSDL_URL As String = _ "https://ksriniv3-mobl/shipyonder/shippingChargesQuote.asmx?WSDL" Private Const c_SERVICE As String = "ShippingRateQuote" Private Const c_PORT As String = "ShippingRateQuoteSoap" Private Const c_SERVICE_NAMESPACE As String = _ "https://www.iiitb.ac.in/webservices/" Private Sub Class_Initialize() '***************************************************************** 'This subroutine will be called each time the class is instantiated. 'Creates sc_ComplexTypes as new SoapClient30, and then 'initializes sc_ComplexTypes.mssoapinit2 with WSDL file found in 'https://ksriniv3-mobl/shipyonder/shippingChargesQuote.asmx?WSDL. '***************************************************************** Dim str_WSML As String str_WSML = "" Set sc_ShippingRateQuote = New SoapClient30 sc_ShippingRateQuote.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, _ c_PORT, c_SERVICE_NAMESPACE 'Use the proxy server defined in Internet Explorer's LAN settings by 'setting ProxyServer to <CURRENT_USER> sc_ShippingRateQuote.ConnectorProperty("ProxyServer") = _ "<CURRENT_USER>" 'Autodetect proxy settings if Internet Explorer is set to autodetect 'by setting EnableAutoProxy to True sc_ShippingRateQuote.ConnectorProperty("EnableAutoProxy") = True End Sub Private Sub Class_Terminate() '***************************************************************** 'This subroutine will be called each time the class is destructed. 'Sets sc_ComplexTypes to Nothing. '***************************************************************** 'Error Trap On Error GoTo Class_TerminateTrap Set sc_ShippingRateQuote = Nothing Exit Sub Class_TerminateTrap: ShippingRateQuoteErrorHandler ("Class_Terminate") End Sub Private Sub ShippingRateQuoteErrorHandler(str_Function As String) 'SOAP Error If sc_ShippingRateQuote.FaultCode <> "" Then Err.Raise vbObjectError, str_Function, _ sc_ShippingRateQuote.FaultString 'Non SOAP Error Else Err.Raise Err.Number, str_Function, Err.Description End If End Sub Public Function wsm_GetRate(ByVal str_city1 As String, _ ByVal str_city2 As String) As Long '***************************************************************** 'Proxy function created from https://ksriniv3-mobl/shipyonder/ 'shippingChargesQuote.asmx?WSDL. '***************************************************************** 'Error Trap On Error GoTo wsm_GetRateTrap wsm_GetRate = sc_ShippingRateQuote.GetRate(str_city1, str_city2) Exit Function wsm_GetRateTrap: ShippingRateQuoteErrorHandler "wsm_GetRate" End Function
The next three private class variables hold the values of the service and port names and the namespace. The first two lines of actual code declare two private class variables: one for the SOAP client and another for the URL to the WSDL file. The SOAP client is created and initialized in the class's Initialize method and is set to Nothing in the class's Terminate method. The Initialize method has a local variable, str_WSML, that maps the Web Service's complex paramaters to classes in VBA. Since there are no complex parameters in this case, it is set to an empty string.
A method is defined for each operation specified within the service. Each of the method names are generated by adding the prefix "wsm_" to the operation name. In my case, I have one method, wsm_GetRate, corresponding to the GetRate operation in my service. The real work is done by this method. It makes the SOAP call to the Web Service and returns the integer value it gets back from the Web method, making it possible to write a simple VBA script to access this method.
You can create a user-defined function, GetShippingRate, that can be used just like Sum, Average, and other intrinsic Excel functions. Insert a module into your project and insert a variable declaration and a public function, as shown in the following code sample:
'Instance of the class generated by the toolkit Dim myRateQuoteObject As New clsws_ShippingRateQuote 'User-defined function that the ShippingRateQuote web service 'through the toolkit generated class's method Public Function GetShippingRate(fromCity As String, toCity As String) _ As Long GetShippingRate = myRateQuoteObject.wsm_GetRate(fromCity, toCity) End Function
Now you can type "=GetShippingRate($A16,C$7)" in cell C16 if you are using the spreadsheet in Figure 1; otherwise, you will need to modify the cell addresses. After entering this formula, copy it to the other cells.
Document-style Web Services
Now let's look at ShippingRateQuote2. This service is similar to the one you saw previously; however, this one accepts quote requests and provides quotes in the form of XML documents.
First I will look briefly at the XML Schema Definition (XSD) file specifying the quote request and quote document formats and at the ASP.NET implementation of the Web Service.Then I'll discuss the code generated by the toolkit to access this service from Excel. The generated code will be longer and more interesting this time!
Figure 5 shows the simple schema for the quote request and quote documents. Real-world standard documents can often be more complex, but I kept it simple here for brevity.
Figure 5 Quote Schema
<?xml version="1.0" ?> <schema targetNamespace="https://ksriniv3-mobl/ShippingRateQuote" xmlns="https://www.w3.org/2001/XMLSchema"> <element name="QuoteRequest"> <complexType> <sequence> <element minOccurs="1" maxOccurs="1" name="strFromCity" nillable="true" type="string" /> <element minOccurs="1" maxOccurs="1" name="strToCity" nillable="true" type="string" /> </sequence> </complexType> </element> <element name="Quote"> <complexType> <sequence> <element minOccurs="1" maxOccurs="1" name="iRate" type="int" /> </sequence> </complexType> </element> </schema>
The ASP.NET Document-style Web Service
Figure 6 contains the .asmx file that provides the new Web Service ShippingRateQuote2 whose parameters are XML documents that adhere to the schema shown in Figure 4. The first change from the .asmx file that provided an RPC-oriented Web Service is the inclusion of the System.XML.Serialization namespace. The most significant change is that the Web method has class-type parameters now. The parameters are instances of the two classes shown at the bottom of the file. In this case, I manually wrote these classes to correspond to the XSD file; however, the wsdl.exe tool in the .NET SDK can be used to generate classes that correspond to a schema. If the standard is not specified using the XSD format, but using an XML Data Reduced (XDR) file or just an instance document, you can translate it into an XSD file using the XSD.exe tool, which is also found in the .NET SDK.
Figure 6 New Web Service
<%@ WebService Language="VB" Class="ShippingRateQuote2"%> Imports System Imports System.Web.Services '.NET Framework class to support XML serialization Imports System.Xml.Serialization <WebService(Namespace:="https://ksriniv3-mobl/shipyonder/")> _ Public Class ShippingRateQuote2 : Inherits WebService <WebMethod()> Public Function GetRate(qr as QuoteRequest) as Quote Dim NewQuote As New Quote() Select Case qr.strFromCity Case "Greenville" Select Case qr.strToCity Case "San Francisco" NewQuote.iRate = 10 Return NewQuote 'Deleted further case statements for brevity End Select End Select End Function End Class 'Classes corresponding to the XML documents 'that are the parameters to this Web Service 'operation Public Class QuoteRequest Public strFromCity As String Public strToCity As String End Class Public Class Quote Public iRate as integer End Class
I registered this service, ShippingRateQuote2, at the Microsoft UDDI site as I did with the previous service.
Developing the Excel Client for the New Service
As before, you should use the Web Services Reference Tool to locate the ShippingRateQuote2 service in UDDI and generate the code by selecting it. This time, the toolkit generates three classes. (Remember it generated only one class for the simple Web Service?) The class struct_QuoteRequest corresponds to the XML document QuoteRequest in my Web Service. Following are the two members of the class with the same name as the elements in the XML document:
Public strFromCity As String Public strToCity As String
Didn't I have two XML types, QuoteRequest and Quote, in my Web Service? Why didn't the toolkit generate a class corresponding to Quote? The Quote type definition in the Web Service contains only one element, iRate, of the type integer (see Figure 5). The toolkit-generated VBA code maps this single integer element to an array of longs. If there were more elements, the toolkit would generate a class corresponding to it as it did for the QuoteRequest. You can verify this process by adding a member to the Quote class in your .asmx file.
The second class generated by the toolkit, clsof_Factory_ShippingRateQ, is used by the SOAP Toolkit 3.0 generic type mapper. You can find a complete description of the generic and other type mappers in the SOAP Toolkit 3.0 documentation. A thorough understanding of how type mappers work is not required for the purposes of this article.
The last class, clsws_ShippingRateQuote2 (see Figure 7), is the proxy to my Web Service ShippingRateQuote2 and is similar to clsws_ShippingRateQuote. The private class members declared at the top are identical to the earlier example. In the initialize method, the str_WSML variable is not an empty string. It contains the information that SOAP uses to serialize and deserialize QuoteRequest information, and points to the MSSOAP.GenericCustomTypeMapper30 type mapper and to struct_QuoteRequest as the class to which the QuoteRequest type should be mapped. The last line of code is also new here. It creates an instance of the clsof_Factory_ShippingRateQ class and sets it as the value of the GCTMObjectFactory client property of the SOAP client object.
Figure 7 ShippingRateQuote2
'***************************************************************** 'Comments deleted. '***************************************************************** 'Dimensioning private class variables. Private sc_ShippingRateQuote2 As SoapClient30 Private Const c_WSDL_URL As String = _ "https://ksriniv3-mobl/shipyonder/shippingChargesQuote2.asmx?WSDL" Private Const c_SERVICE As String = "ShippingRateQuote2" Private Const c_PORT As String = "ShippingRateQuote2Soap" Private Const c_SERVICE_NAMESPACE As String = _ "https://ksriniv3-mobl/shipyonder/" Private Sub Class_Initialize() '***************************************************************** 'This subroutine will be called each time the class is instantiated. 'Creates sc_ComplexTypes as new SoapClient30, and then 'initializes sc_ComplexTypes.mssoapinit2 with WSDL file found in 'https://ksriniv3-mobl/shipyonder/shippingChargesQuote2.asmx?WSDL. '***************************************************************** Dim str_WSML As String str_WSML = "<servicemapping>" str_WSML = str_WSML & "<service name='ShippingRateQuote2'>" str_WSML = str_WSML & _ "<using PROGID='MSSOAP.GenericCustomTypeMapper30' _ cachable='0' ID='GCTM'/>" str_WSML = str_WSML & "<types>" str_WSML = str_WSML & "<type name='QuoteRequest'" & _ "targetNamespace='https://ksriniv3-mobl/shipyonder/' uses='GCTM'" & _ "targetClassName='struct_QuoteRequest'/>" str_WSML = str_WSML & "</types>" str_WSML = str_WSML & "</service>" str_WSML = str_WSML & "</servicemapping>" Set sc_ShippingRateQuote2 = New SoapClient30 sc_ShippingRateQuote2.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, _ c_PORT, c_SERVICE_NAMESPACE 'Use the proxy server defined in Internet Explorer's LAN settings by 'setting ProxyServer to <CURRENT_USER> sc_ShippingRateQuote2.ConnectorProperty("ProxyServer") = _ "<CURRENT_USER>" 'Autodetect proxy settings if Internet Explorer is set to autodetect 'by setting EnableAutoProxy to True sc_ShippingRateQuote2.ConnectorProperty("EnableAutoProxy") = True Set sc_ShippingRateQuote2.ClientProperty("GCTMObjectFactory") = _ New clsof_Factory_ShippingRateQ End Sub Private Sub Class_Terminate() 'Error Trap On Error GoTo Class_TerminateTrap Set sc_ShippingRateQuote2 = Nothing Exit Sub Class_TerminateTrap: ShippingRateQuote2ErrorHandler ("Class_Terminate") End Sub Private Sub ShippingRateQuote2ErrorHandler(str_Function As String) 'SOAP Error If sc_ShippingRateQuote2.FaultCode <> "" Then Err.Raise vbObjectError, str_Function, _ sc_ShippingRateQuote2.FaultString 'Non SOAP Error Else Err.Raise Err.Number, str_Function, Err.Description End If End Sub Public Function wsm_GetRate(ByVal obj_qr As struct_QuoteRequest) As Variant '***************************************************************** 'Proxy function created from https://ksriniv3-mobl/shipyonder/ 'shippingChargesQuote2.asmx?WSDL. ' '"wsm_GetRate" is an array with elements defined as Long 'See Complex Types: Arrays in Web Service References Tool 2.0 Help 'for details on implementing arrays. '***************************************************************** 'Error Trap On Error GoTo wsm_GetRateTrap wsm_GetRate = sc_ShippingRateQuote2.GetRate(obj_qr) Exit Function wsm_GetRateTrap: ShippingRateQuote2ErrorHandler "wsm_GetRate" End Function
The wsm_GetRate function is the most important part of the generated code, since this is the function I will be calling from my VBA scripts. It takes an object of the type struct_QuoteRequest as input and returns an array of longs as a variant. It calls the SOAP client object with the same input and return values. The SOAP Toolkit 3.0 generic type mapper performs the magic in serializing and deserializing to the Web Service.
Wrapping Up the Excel Transportation App
Finally, let's go back to the user-defined function GetShippingRate in the Excel VBA project and modify it so that it obtains quotes from both the services and returns the lesser of the two. Figure 8 shows the new code.
Figure 8 New GetShippingRate Code
'Instances of the two classes generated by the Web Services Toolkit Dim myRateQuoteObject As New clsws_ShippingRateQuote Dim myRateQuoteObject2 As New clsws_ShippingRateQuote2 'User-defined function that calls the ShippingRateQuote Web Service 'as well as the ShippingRateQuote2 Web Service 'through the WSTK generated class's method. 'It compares the quotes form the two services and sends the lesser of the 'two back. Public Function GetShippingRate(fromCity As String, toCity As String) _ As Long 'Call the Web Service with simple parameter types quote1 = myRateQuoteObject.wsm_GetRate(fromCity, toCity) 'Call the Web Service with the complex parameters Dim qRequest As New struct_QuoteRequest qRequest.strFromCity = fromCity qRequest.strToCity = toCity 'Input is the object of type struct_QuoteRequest 'Output is an array. The first value in it is the quote. quote2 = myRateQuoteObject2.wsm_GetRate(qRequest)(0) If quote1 <= quote2 Then GetShippingRate = quote1 Else GetShippingRate = quote2 End If End Function
A bonus of using Excel, or any other Office tool for that matter, is that you get persistence for free. Once you have obtained the lowest quotes, you can do further analyses on the data even when you are not connected to the Internet.
Figure 9** Final Excel Spreadsheet **
Since the transportation problem is one of the examples provided for Excel Solver, I will not go into the details of building the model. However, the Excel file for download at the MSDN® Magazine Web site has the model and still includes extensive comments on the model from the original Solver example file. The final solution is shown in Figure 9. The quantity to be shipped between each plant and distribution center is shown in cells C8 to G10 and the minimum shipping cost is shown in cell B20. The Office XP Web Services Toolkit allowed me to exploit the synergy between the unique capabilities of Excel and the strengths of Web Services without much effort.
Conclusion
Though I have only used Excel in my example, the toolkit can be used from all of the Office applications. In fact, the toolkit comes with some interesting samples for all of these products.
The Office XP Web Services Toolkit is a significant development in many respects—it makes Web Services accessible from the ubiquitous Office tools with minimal coding and thus lets users access Web Services from the already-familiar Office family of tools.
For related articles see:
XML Web Services Basics
Web Services Description Language
Simple Object Access Protocol
For background information see:
Using WSDL in a UDDI Registry
HOW TO: Create and Test an XML Web Service in Visual Basic .NET
Krishnamurthy Srinivasanmanages the Web Service Technologies group in the Intel Labs. His group represents Intel in key Web Service standards bodies. During his recent sabbatical, he taught a graduate course at the Indian Institute of Information Technology, Bangalore.