Using Complex Data Types with XML Web Services in Microsoft Excel 2002This 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.
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.
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.
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.
Figure 1. The ZipCodeWeatherReport.xls sample.
- 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.
- To obtain weather condition reports for specific Zip Codes, type the Zip Codes in Column A.
- Click Get Weather Report. Column B to G will be populated with the latest weather condition details.
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 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
LastUpdatedis the date and time the weather condition data was last updated.
IconUrlis 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.
Conditionsgive the current weather condition, for example, clear or overcast.
CurrentTempreturns the current temperature.
Humiditygives the current humidity condition.
Barometergives the current barometer reading.
BarometerDirectiongives 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:
clsWeatherrepresents the XML Web service.
objCurWeatherrepresents the complex data type structure of the weather condition values.
rngZipCodesrepresents all the cells in Column A.
rngZiprepresents 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
struct_CurrentWeather classes are generated by the Web Service References Tool 2.0.
Set clsWeather = New clsws_WeatherRetriever Set objCurWeather = New struct_CurrentWeather
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
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.
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:
- Start Microsoft Excel 2002.
- On the Tools menu, point to Macro, and then click Security. This displays the Security dialog box.
- On the Security Level tab, click Medium.
- Click the Trusted Sources tab. Uncheck Trust all installed add-ins and templates.
- 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:
- Copy the WeatherRetriever WSDL URL http://www.learnxmlws.com/services/weatherretriever.asmx?wsdl to the Clipboard.
- Press ALT+F11 to get to the VBA editor.
- On the Tools menu, click Web Service References.
- Paste the WSDL URL into the Web Service URL edit box
- 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.
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.