Using Complex Data Types with XML Web Services in Microsoft Excel 2002

  • Article

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.

 

Siew-Moi Khor
Microsoft Corporation

July 2002

Applies to:
    Microsoft® Excel 2002

Summary: This article describes an XML Web service sample that checks weather reports over the Internet using the Microsoft Office XP Web Services Toolkit 2.0 and Microsoft Excel 2002.

Download setup.exe.

Contents

Introduction
Running the Solution
Code Walkthrough
Troubleshooting
Conclusion

Introduction

The Microsoft Office XP Web Services Toolkit 2.0 greatly simplifies the process of accessing and utilizing an XML Web service from a client application. The Web Service References Tool 2.0 included in the toolkit allows a client application like Microsoft Excel 2002 to easily create a Microsoft Visual Basic for Applications® (VBA) proxy class and other relevant classes from either a WSDL file or Visual Studio .NET discovery (.vsdisco) file. For detailed information about the toolkit, see the Using the Office XP Web Services Toolkit 2.0 to Interact with Complex Types in XML Web Services article and the Help file included in the toolkit.

The proxy class contains methods exposed by an XML Web service, thus allowing you to invoke and interact with the XML Web services provided through the exposed methods. The sample described in this article uses the Web Service References Tool 2.0 to create the proxy class and other relevant files. This sample calls the WeatherRetriever XML Web service provided by XMethods to check weather conditions over the Internet using Excel 2002. This article assumes that you already have the Microsoft Office XP Web Services Toolkit 2.0 on your computer.

Running the Sample

After downloading the sample, open the ZipCodeWeatherReport.xls file using Excel 2002. You will find a spreadsheet with weather condition reports for five different Zip Codes as shown in Figure 1.

Click here for larger image

Figure 1. The ZipCodeWeatherReport.xls sample.

  1. To run this sample, first clear the worksheet by clicking the Clear Worksheet button. This will clear all data on the worksheet except the Zip Code values in Column A.
  2. To obtain weather condition reports for specific Zip Codes, type the Zip Codes in Column A.
  3. Click Get Weather Report. Column B to G will be populated with the latest weather condition details.

Code Walkthrough

The WeatherRetriever XML Web service provides a method called GetWeather that accepts a Zip Code as a String value. It then returns a complex data type structure called struct_CurrentWeather. The struct_CurrentWeather complex data type structure consists of four String values and three Single values as shown below in the code snippet taken from the struct_CurrentWeather class module.

Public LastUpdated As String
Public IconUrl As String
Public Conditions As String
Public CurrentTemp As Single
Public Humidity As Single
Public Barometer As Single
Public BarometerDirection As String
  • LastUpdated is the date and time the weather condition data was last updated.
  • IconUrl is the URL to an icon depiction of the current weather condition, for example sunny or cloudy. In this sample, we won't be retrieving this information from the XML Web service.
  • Conditions give the current weather condition, for example, clear or overcast.
  • CurrentTemp returns the current temperature.
  • Humidity gives the current humidity condition.
  • Barometer gives the current barometer reading.
  • BarometerDirection gives which way the barometer reading is currently heading toward, for example, falling or rising.

Let's examine the VBA code that calls the WeatherRetriever XML Web service. First, four objects are declared:

    Dim clsWeather As clsws_WeatherRetriever
    Dim objCurWeather As struct_CurrentWeather
    Dim rngZipCodes As Range
    Dim rngZip As Range
  • The declared variable representations are as follows:
    • clsWeather represents the XML Web service.
    • objCurWeather represents the complex data type structure of the weather condition values.
    • rngZipCodes represents all the cells in Column A.
    • rngZip represents the active cell Zip Code.

In the code snippet below, the clsWeather object variable is initialized to an instance of the clsws_WeatherRetriever object which is the VBA proxy class. Also the objCurWeather object is initialized to an instance of the struct_CurrentWeather object which, as discussed earlier, will return a complex data type structure. Both the clsws_WeatherRetriever and struct_CurrentWeather classes are generated by the Web Service References Tool 2.0.

Set clsWeather = New clsws_WeatherRetriever
Set objCurWeather = New struct_CurrentWeather

Next rngZipCodes is set to include cell A2 to A65536. Then cell A2, which contains the first Zip Code, is set as the active cell as shown below.

Set rngZipCodes = Range(Range("a2"), Range("a65536").End(xlUp)) Application.ActiveSheet.Range("A2").Activate

Each cell in column A, starting with cell A2, is checked for the presence of a Zip Code value. If there is no Zip Code value, the subroutine ends.

For Each rngZip In rngZipCodes
     ...
        With rngZip
            ...
        End With
    
        'Go to the next zip code.
        Next rngZip
    Exit Sub

If there is a Zip Code value, the Zip Code is passed as a parameter to the wsm_GetWeather method in the clsws_WeatherRetriever class, which maps to the XML Web service's GetWeather method. The wsm_GetWeather method passes the Zip Code to the XML Web service, and returns a complex data type structure struct_CurrentWeather. These values are assigned to the objCurWeather variable which was initialized earlier by setting it.

The weather condition values returned are placed next to the Zip Code value. The code moves to the next Zip Code value in column A, makes that Zip Code cell active, and repeats the process. The code snippet for this subroutine is as shown below:

    For Each rngZip In rngZipCodes
      Set objCurWeather = clsWeather.wsm_GetWeather(rngZip)
        With rngZip
            .Offset(0, 1).Value = objCurWeather.CurrentTemp
            .Offset(0, 2).Value = objCurWeather.Conditions
            .Offset(0, 3).Value = objCurWeather.Humidity
            .Offset(0, 4).Value = objCurWeather.Barometer
            .Offset(0, 5).Value = objCurWeather.BarometerDirection
            .Offset(0, 6).Value = objCurWeather.LastUpdated
        End With
     Next rngZip
Exit Sub

The wsm_GetWeather method call corresponds to the following code in the clsws_WeatherRetriever class module:

...
Private sc_WeatherRetriever As SoapClient30
Private Const c_WSDL_URL As String = "http://www.learnxmlws.com/services/weatherretriever.asmx?wsdl"
...
    Set sc_WeatherRetriever = New SoapClient30
    sc_WeatherRetriever.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, c_SERVICE_NAMESPACE
...
Public Function wsm_GetWeather(ByVal str_zipCode As String) As struct_CurrentWeather
...
    Set wsm_GetWeather = sc_WeatherRetriever.GetWeather(str_zipCode)
...
End Function

Two command buttons are used in this sample. There are also procedures to clear the worksheet and to do error handling. The very convenient data validation functionality in Excel 2002 is also utilized. If you examine the sample code, which is well commented, you will see how these can be done easily in Excel 2002.

Troubleshooting

Here are some troubleshooting tips that you might find useful:

  • If you are behind a proxy server, you will need the latest proxy client installed on your computer. For example, if you are behind a Microsoft Internet Security & Acceleration (ISA) Server, you will need the ISA Firewall Client installed.
  • The macros are unsigned, so you must have your security set to Medium, with the Trust all installed add-ins and templates settings disabled. At this security setting, you will be prompted either to enable or disable the ZipCodeWeatherReport.xls. To run it, click Enable.

**Note   **After looking at the sample, reset the security setting to whatever level they were at prior to that by making the appropriate changes in the Security dialog box as shown in the steps below.

  • To change the security settings:
    1. Start Microsoft Excel 2002.
    2. On the Tools menu, point to Macro, and then click Security. This displays the Security dialog box.
    3. On the Security Level tab, click Medium.
    4. Click the Trusted Sources tab. Uncheck Trust all installed add-ins and templates.
    5. Quit Excel.
      • If the WeatherRetriever XML Web service does not seem to be working, if for example you get an error message saying the XML Web service is not available at the moment or connection timed out, here is a test you can try:
    6. Copy the WeatherRetriever WSDL URL http://www.learnxmlws.com/services/weatherretriever.asmx?wsdl to the Clipboard.
    7. Press ALT+F11 to get to the VBA editor.
    8. On the Tools menu, click Web Service References.
    9. Paste the WSDL URL into the Web Service URL edit box
    10. Click Search.

If the WeatherRetriever XML Web service methods are returned in the Search Results window, that means you can access the XML Web service and therefore there must be some other problem. Sometimes latency problems such as slow Internet access to the XML Web service can cause failures. If the XML Web service is not found, but you can browse to the WSDL URL using Microsoft Internet Explorer or some other browser, then either your proxy client is not installed or not working.

Conclusion

The Web Service References Tool 2.0 reduces the amount of coding a developer leveraging an XML Web service has to do. As you can see from this article, it is very easy to create a sample that utilizes and interacts with an XML Web service from a client application like Excel 2002.