Using the Office XP Web Services Toolkit 2.0 for Offline or Intermittently Connected Scenarios

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.

Michael Stowe
Microsoft Corporation

July 2002

Applies to:
    Microsoft® Office XP Web Services Toolkit 2.0

Summary: Learn how to use the Office XP Web Services Toolkit 2.0 to interact with XML Web services data in offline or intermittently connected scenarios through the use of document properties and XML configuration files.

Download Offwstk.exe.

Contents

Introduction
When to Consider Caching Data
The SalesRankNPrice Web Service
Caching Data in an XML Document
Caching Data in Custom Document Properties
Conclusion

Introduction

The Microsoft® Office XP Web Services Toolkit 2.0 makes it easy for you to utilize the functionality of any XML Web service from Microsoft Visual Basic® for Applications in your Office solution. One potential obstacle to integrating XML Web services into your Office solution is that many of your target users don't have a persistent network connection. This article discusses a couple of approaches that you can use to cache data returned from XML Web services for use when your user is offline.

When to Consider Caching Data

The word cache implies a strategy used to improve the performance of an application. Usually, this means checking for common data requests before calling CPU or memory-intensive code.

In this article, the word cache refers to storing data returned by an XML Web service locally. Your users won't notice their network connection is unavailable, therefore enhancing the user experience (and in their eyes, performance).

The article XML Web Service Caching Strategies contains an excellent discussion of conditions that you should consider before implementing a caching mechanism.

The SalesRankNPrice Web Service

The example provided with this article utilizes the SalesRankNPrice XML Web service. The SalesRankNPrice XML Web service provides a number of methods that can be used to retrieve the price and the sales ranking of a book from the online retailers Amazon.com and Barnes & Noble.com.

The example illustrates how to cache the data returned by theGetAmazonAndBNPricemethod. This method accepts the ISBN number of a book, and returns the price of the book as reported by Amazon.com and Barnes & Noble.com.

The Web Service References tool creates several classes that expose the functionality of the SalesRankNPrice XML Web service. The classclsws_SalesRankNPricecontains proxy functions that are used to call the corresponding methods on the server. The classclsof_Factory_SalesRankNPricemaps classes containing user-defined types on the client to a corresponding COM object on the Web server. A class is created for each of the complex data types supported by the XML Web service. TheGetAmazonAndBNPricemethod returns the data defined in thestruct_Pricesclass.

Caching Data in an XML Document

One approach to caching data for offline storage and retrieval is to store the data in an XML document. Using an XML document to cache the data returned from a Web service is convenient if you need to access the data from more than one application or document.

Two classes,cls_SalesRankNPriceXMLand cls_wsXMLFileCache, are used to implement caching data to an XML document. Thecls_SalesRankNPriceXMLclass contains a proxy function for theGetAmazonAndBNPricemethod that is to be used in the place of theclsws_SalesRankNPriceclass's proxy function if you intend to store data in, or retrieve from an XML file cache. Thecls_wsXMLFileCacheclass contains the methods necessary to store and retrieve data to and from the cache.

**Note   **The

cls_SalesRankNPriceXML

class also contains the same initialization, termination and error handling routines that the Web Services Toolkit created for the

clsws_SalesRankNPrice

class. These routines will not be listed in the body of this article.

The SalesRankNPriceXML Class

The proxy functionwsm_GetAmazonAndBNPriceis listed below. The function declaration reveals the function's two parameters: str_ISBN and UseCache. The str_ISBN parameter is a String value that represents the ISBN number of a book. The optional UseCache parameter is used to signify whether to retrieve the sales rank of the book represented by str_ISBN from the local cache or from the SalesRankNPrice XML Web service.

The function declaration also reveals that the return data type is struct_Prices. Thestruct_Pricesdata type is a structure that contains two values, the price of the book at Amazon.com (AmazonPrice) and the price of the book at Barnes & Noble.com (BNPrice). The data type is defined in thestruct_Pricesclass.

Public Function wsm_GetAmazonAndBNPrice(ByVal str_ISBN As String, _
                Optional ByVal UseCache As Boolean) As struct_Prices

    ' Error Trap
    On Error GoTo wsm_GetAmazonAndBNPriceTrap

    ' Allocate a variable to store the returned prices.
    Dim structResults As struct_Prices
    Set structResults = New struct_Prices

Once the function has set up an error handler and initialized a variable to store the returned data, thecls_wsXMLFileCacheclass in instantiated. Then, the UseCache parameter is used to determine the method of data retrieval. If the parameter is True, then thecls_wsXMLFileCacheclass'sGetCachedDatamethod is invoked. Otherwise, theSalesRankNPriceWeb method is invoked and the results are then passed to thecls_wsXMLFileCacheclass'sCacheDatamethod.

    ' Use XML data caching.
    Set objcls_wsXMLFileCache = New cls_wsXMLFileCache
    
    If UseCache = True Then

        ' Use cached data.
        Set structResults = objcls_wsXMLFileCache. _
                            GetCachedData("wsm_GetAmazonAndBNPrice", _
                            c_WSDL_URL, str_ISBN)
    Else

        ' Use XML Web service to retrieve the data.
        Set structResults = sc_SalesRankNPrice. _
                            GetAmazonAndBNPrice(str_ISBN)

        ' Cache the data to an XML data file.
        objcls_wsXMLFileCache.CacheData "wsm_GetAmazonAndBNPrice", _
            c_WSDL_URL, str_ISBN, structResults
    End If

Finally, the retrieved prices are returned to the calling function.

    ' Return results to calling procedure.
    Set wsm_GetAmazonAndBNPrice = structResults

Exit Function
wsm_GetAmazonAndBNPriceTrap:
    SalesRankNPriceErrorHandler "wsm_GetAmazonAndBNPrice"
End Function

The wsXMLFileCache Class

The wsXMLFileCache class contains two public methods,CacheDataand GetCachedData, and one supporting function, VerifyCacheExists. TheCacheDatamethod creates and manages the cache (WebCache.xml). TheGetCachedDatamethod is used to retrieve data from the cache.

The General Declarations section of the class is used to declare a module-level variable representing the Microsoft XML version 4.0 Document Object Library (DOM). Also, a constant is defined as the name of the XML document that stores the cached data.

Private objDom As MSXML2.DOMDocument40
Private Const c_CACHED_DATA_FILENAME As String = "WebCache.Xml"

TheCacheDatamethod accepts four parameters. The strMethod parameter represents the Web method that was used to return the data to cache. The strWSDL method represents the path to the XML Web service's WSDL file. The strParameterList parameter represents the ISBN number that was passed to the XML Web service. The StructResults parameter represents the data returned by the XML Web service.

Public Function CacheData(ByVal strWebMethod As String, _
                          ByVal strWSDLPath As String, _
                          ByVal strParameterList As String, _
                          ByVal structResults As struct_Prices) _
                          As Boolean

    ' Create a new XML document. 
    Set objDom = New MSXML2.DOMDocument40
    Dim objElm As MSXML2.IXMLDOMNode
    Dim objChild As MSXML2.IXMLDOMNode
    Dim objList As MSXML2.IXMLDOMNode

TheVerifyCacheExistsfunction is used to confirm the presence of the XML cache. If the cache file doesn't exist, then the function creates the file in the current directory. The file is then loaded into memory.

    Call VerifyCacheExists(CurDir & "\" & c_CACHED_DATA_FILENAME)
    objDom.Load CurDir & "\" & c_CACHED_DATA_FILENAME

The XML DOM's selectSingleNode method is used to determine whether a node for the specified ISBN number already exists. If the node doesn't exist, then the appropriate elements and nodes are created for the specified ISBN number.

    If objDom.selectSingleNode(".//webmethod[name='" & _
            strWebMethod & "'and wsdlpath='" & _
            strWSDLPath & "' and parameters= '" & _
            strParameterList & "']/AmazonPrice") Is Nothing Then

        ' If no matching XML document exists, create new XML document
        ' and cache the data.
        ' <webmethod>
        Set objChild = objDom.createElement("webmethod")
        Set objElm = objDom.documentElement.appendChild(objChild)
        ' <name>
        Set objChild = objDom.createElement("name")

        objChild.nodeTypedValue = strWebMethod
        objElm.appendChild objChild

        ' <wsdlpath>
        Set objChild = objDom.createElement("wsdlpath")

        objChild.nodeTypedValue = strWSDLPath
        objElm.appendChild objChild

        ' <parameters>
        Set objChild = objDom.createElement("parameters")

        objChild.nodeTypedValue = strParameterList
        objElm.appendChild objChild

        ' <AmazonPrice>
        Set objChild = objDom.createElement("AmazonPrice")
        objChild.nodeTypedValue = structResults.AmazonPrice
        objElm.appendChild objChild

        ' <BNPrice>
        Set objChild = objDom.createElement("BNPrice")
        objChild.nodeTypedValue = structResults.BNPrice
        objElm.appendChild objChild

        ' Commit the changes to disk.
        objDom.Save CurDir & "\" & c_CACHED_DATA_FILENAME

    Else

If a node for the specified ISBN number already exists, then the data is cached to the existing node.

        ' Cache data to existing node.
        Set objList = objDom.selectSingleNode(".//webmethod[name='" & _
                     strWebMethod & "'and wsdlpath='" & strWSDLPath & _
                     "' and parameters= '" & strParameterList & _
                     "']/AmazonPrice")

        ' Add the Amazon.com price to the existing node.
        objList.childNodes(0).nodeValue = structResults.AmazonPrice

       Set objList = objDom.selectSingleNode(".//webmethod[name='" & _
            strWebMethod & "'and wsdlpath='" & strWSDLPath & _
            "' and parameters= '" & strParameterList & "']/BNPrice")

        ' Add the Barnes and Noble.com price to the existing node.
        bjList.childNodes(0).nodeValue = structResults.BNPrice

        ' Commit the changes to disk.
        objDom.Save CurDir & "\" & c_CACHED_DATA_FILENAME

    End If
    
End Function

TheGetCachedDatamethod accepts three parameters. ThestrWebMethodparameter represents the Web method that was used to return the data to cache. ThestrWSDLPathmethod represents the path to the XML Web service's WSDL file. ThestrParameterListparameter represents the ISBN number that was passed to the XML Web service. The cached data is returned as thestruct_Pricesdata type.

Public Function GetCachedData(ByVal strWebMethod As String, _
                              ByVal strWSDLPath As String, _
                              ByVal strParameterList As String) _
                              As struct_Prices

    ' Create a new XML document.
    Set objDom = New MSXML2.DOMDocument40
    Dim objList As MSXML2.IXMLDOMNode
    Dim structCachedPrices As New struct_Prices

TheVerifyCacheExistsfunction is used to confirm the presence of the XML cache. If the cache file doesn't exist, then the function creates the file in the current directory. The file is then loaded into memory.

    Call VerifyCacheExists(CurDir & "\" & c_CACHED_DATA_FILENAME)
    objDom.Load CurDir & "\" & c_CACHED_DATA_FILENAME

The XML DOM's selectSingleNode method is used to determine whether a node for the specified ISBN number already exists. If the node doesn't exist, then an error is raised.

    Set objList = objDom.selectSingleNode(".//webmethod[name='" & _
        strWebMethod & "'and wsdlpath='" & _
        strWSDLPath & "' and parameters= '" & _
        strParameterList & "']/AmazonPrice")

    If objList Is Nothing Then
        ' Raise error. Cached data can't be found.
        Err.Raise vbObjectError + 1, , "Cached data cannot be located."
    Else

If the specified node does exist, then
        structCachedPrices.AmazonPrice = objList.Text

        Set objList = objDom.selectSingleNode(".//webmethod[name='" & _
            strWebMethod & "'and wsdlpath='" & _
            strWSDLPath & "' and parameters= '" & _
            strParameterList & "']/BNPrice")

        structCachedPrices.BNPrice = objList.Text

        Set GetCachedData = structCachedPrices
    End If

End Function

Using the SalesRankNPriceXML Class to Cache and Retrieve Data

The following procedure illustrates how to use theGetAmazonAndBNPricemethod provided by thecls_SalesRankNPriceXMLclass to retrieve and cache prices for a list of ISBN numbers stored in a worksheet.

Sub RetrieveAndCacheInXML()

    Dim objPrices As cls_SalesRankNPriceXML
    Dim rngISBN As Range
    Dim rngCell As Range

    ' Set the range ISBN numbers.
    Set rngISBN = Worksheets("Sheet1").Range("a2", _
                  Worksheets("Sheet1").Range("a2").End(xlDown))

    ' Create a new instance of the SalesRankNPriceXML class.
    Set objPrices = New cls_SalesRankNPriceXML

    ' Loop through the ISBN numbers
    For Each rngCell In rngISBN

         ' Retrieve and cache the prices for the specified ISBN number.
         objPrices.wsm_GetAmazonAndBNPrice rngCell.Value

    Next rngCell

End Sub

The following procedure illustrates how to retrieve the prices for the selected ISBN number from the XML cache.

Sub RetrieveFromXMLCache()

    Dim objPrices As cls_SalesRankNPriceXML
    Dim objResults As struct_Prices

    ' Create a new instance of the SalesRankNPriceXML class.
    Set objPrices = New cls_SalesRankNPriceXML

    ' Retrieve the prices from the cache.
    Set objResults = objPrices.wsm_GetAmazonAndBNPrice _
                     (ActiveCell.Value, True)
    ' Display the prices.
    MsgBox "The prices for ISBN " & ActiveCell.Value & " are:" _
           & Chr(13) & "Amazon.com: " & objResults.AmazonPrice _
           & Chr(13) & "BN.Com: " & objResults.BNPrice

End Sub

Caching Data in Custom Document Properties

Another approach to caching data for offline storage and retrieval is to store the data in custom document properties. This approach may be preferable to the XML document approach if you don't want to manage an external file.

Two classes,cls_SalesRankNPriceDocPropsand cls_wsDocPropCache, are used to implement caching data to an XML document. Thecls_SalesRankNPriceDocPropsclass contains a proxy function for theGetAmazonAndBNPricemethod that is to be used in the place of theclsws_SalesRankNPriceclass's proxy function if you intend to store data in, or retrieve data from, custom document properties. Thecls_wsDocPropCacheclass contains the methods necessary to store and retrieve data to and from the custom document properties.

Note   The

cls_SalesRankNPriceDocProps

class also contains the same initialization, termination, and error handling routines that the Web Services Toolkit created for the

clsws_SalesRankNPrice

class. These routines will not be listed in the body of this article.

The SalesRankNPriceDocProps Class

The proxy function wsm_GetAmazonAndBNPrice, is listed below. The function is structured exactly like thewsm_GetAmazonAndBNPriceclass'swsm_GetAmazonAndBNPricefunction.

Public Function wsm_GetAmazonAndBNPrice(ByVal str_ISBN As String, _
                Optional ByVal UseCache As Boolean) As struct_Prices

    ' Error Trap.
    On Error GoTo wsm_GetAmazonAndBNPriceTrap

    ' Allocate a variable to store the returned prices.
    Dim structResults As struct_Prices
    Set structResults = New struct_Prices

    ' Use document property data caching.
    Set objcls_wsDocPropCache = New cls_wsDocPropCache

    If UseCache = True Then
        ' Use cached data.
        Set structResults = objcls_wsDocPropCache. _
                            GetCachedData("wsm_GetAmazonAndBNPrice", _
                            c_WSDL_URL, str_ISBN)
    Else
        ' Use XML Web service to retrieve the data.
        Set structResults = sc_SalesRankNPrice. _
                            GetAmazonAndBNPrice(str_ISBN)

        ' Cache the data to document properties.
        objcls_wsDocPropCache.CacheData "wsm_GetAmazonAndBNPrice", _
            c_WSDL_URL, str_ISBN, structResults
    End If

    ' Return results to calling procedure.
    Set wsm_GetAmazonAndBNPrice = structResults

Exit Function
wsm_GetAmazonAndBNPriceTrap:
    SalesRankNPriceErrorHandler "wsm_GetAmazonAndBNPrice"
End Function

The wsDocPropCache Class

ThewsDocPropCacheclass contains two public methods,CacheDataand GetCachedData. TheCacheDatamethod creates and manages custom document properties that comprise the cache. TheGetCachedDatamethod is used to retrieve data from the cache.

The General Declarations section of the class is used to declare a module-level variable representing the Microsoft XML version 4.0 Document Object Library (DOM).

Private objDom As MSXML2.DOMDocument40

TheCacheDatamethod accepts four parameters. The strMethod parameter represents the Web method that was used to return the data to cache. The strWSDL method represents the path to the XML Web service's WSDL file. The strParameterList parameter represents the ISBN number that was passed to the XML Web service. The StructResults parameter represents the data returned by the XML Web service.

Public Sub CacheData(ByVal strWebMethod As String, _
                     ByVal strWSDLPath As String, _
                     ByVal strParameterList As String, _
                     ByVal structResults As struct_Prices)

    Dim objProps As Office.DocumentProperties
    Dim objProp As Office.DocumentProperty
    Dim strProp As String
    Dim strPropValue As String

' Set a variable to the CustomDocumentProperties collection.
    Set objProps = Application.ActiveWorkbook.CustomDocumentProperties

The following code establishes the name and value of the custom document property. The name of the property is a combination of the Web method used, the path to the XML Web service's WSDL file and the ISBN number of the book. The custom document property's value is a string containing the Amazon.com and the Barnes & Noble.com prices for the book, separated by a comma.

    strProp = strWebMethod & "+" & strWSDLPath & "+" & strParameterList
    strPropValue = structResults.AmazonPrice & "," & _
                   structResults.BNPrice

To determine whether or not the custom document property exists, a For Each . . . Next construct is used to loop through the CustomDocumentProperties collection of the document. If the property already exists, its value is updated. If the property isn't found in the CustomDocumentProperties collection, then a new property is added to the document.

    For Each objProp In objProps

        ' Does the property exist?
        If objProp.Name = strProp Then

            ' Set the value of the property
            objProp.Value = strPropValue

            Exit Sub
        End If
    Next objProp
        
    ' Create a new custom property.
    objProps.Add strProp, False, msoPropertyTypeString, strPropValue
        
End Sub

TheGetCachedDatamethod accepts three parameters. ThestrWebMethodparameter represents the Web method that was used to return the data to cache. ThestrWSDLPathmethod represents the path to the XML Web service's WSDL file. ThestrParameterListparameter represents the ISBN number that was passed to the XML Web service. The cached data is returned as thestruct_Pricesdata type.

Public Function GetCachedData(ByVal strWebMethod As String, _
                              ByVal strWSDLPath As String, _
                              ByVal strParameterList As String) _
                              As struct_Prices

    Dim objProps As Office.DocumentProperties
    Dim objProp As Office.DocumentProperty
    Dim strProp As String
    Dim strAmazonPrice As String
    Dim strBNPrice As String
    Dim structPrices As New struct_Prices
    Dim iComma As Integer

    ' Set a variable to the property that is being searched for.
    strProp = strWebMethod & "+" & strWSDLPath & "+" & strParameterList

    Set objProps = Application.ActiveWorkbook.CustomDocumentProperties

The following code attempts to find the requested custom document property by looping through the document's CustomDocumentProperties collection. If the property is found, then its value is parsed into the correct components. The values are then assigned to the appropriate members of thestructPricesvariable. Finally, the values are returned to the calling procedure.

    For Each objProp In objProps

        If objProp.Name = strProp Then

            iComma = InStr(1, objProp.Value, ",")
            strAmazonPrice = Left(objProp.Value, iComma - 1)
            strBNPrice = Right(objProp.Value, Len(objProp.Value) _
                         - iComma)


            structPrices.AmazonPrice = strAmazonPrice
            structPrices.BNPrice = strBNPrice

            Set GetCachedData = structPrices
            Exit Function
        End If
    Next objProp

    ' Raise an error indicating that the requested data can't be found.
    Err.Raise vbObjectError + 1, , "Cached data cannot be located" & _
                                   "for the requested item."

End Function

Using the SalesRankNPriceDocProps Class to Cache and Retrieve Data

The following procedure illustrates how to use theGetAmazonAndBNPricemethod provided by thecls_SalesRankNPriceDocPropsclass to retrieve and cache prices for a list of ISBN numbers stored in a worksheet.

Sub RetrieveAndCacheInDocumentProperties()

    Dim objPrices As cls_SalesRankNPriceDocProps
    Dim rngISBN As Range
    Dim rngCell As Range

    ' Set the range ISBN numbers.
    Set rngISBN = Worksheets("Sheet1").Range("a2", _
                  Worksheets("Sheet1").Range("a2").End(xlDown))

    ' Create a new instance of the SalesRankNPriceDocProps class.
    Set objPrices = New cls_SalesRankNPriceDocProps

    ' Loop throught the ISBN numbers.
    For Each rngCell In rngISBN

         ' Retrieve and cache the prices for the specified ISBN number.
         objPrices.wsm_GetAmazonAndBNPrice rngCell.Value

    Next rngCell

End Sub

The following procedure illustrates how to retrieve the prices for the selected ISBN number from the cache of custom document properties.

Sub RetrieveFromDocumentProperties()

    Dim objPrices As cls_SalesRankNPriceDocProps
    Dim objResults As struct_Prices

    ' Create a new instance of the SalesRankNPriceXML class.
    Set objPrices = New cls_SalesRankNPriceDocProps

    ' Retrieve the prices from the cache.
    Set objResults = objPrices.wsm_GetAmazonAndBNPrice _
                     (ActiveCell.Value, True)

   ' Display the prices.
   MsgBox "The prices for ISBN " & ActiveCell.Value & " are:" _
           & Chr(13) &  "Amazon.com: " & objResults.AmazonPrice _
           & Chr(13) &  "BN.Com: " & objResults.BNPrice

End Sub

Conclusion

This article demonstrated two approaches that you can use to cache data returned from an XML Web service to better support users who are not always online.