Consuming Web Services from Excel 2003
It's possible to consume web services from Excel 2003, but few of the sources seem to cover the topic from end-to-end. This post is intended as a companion to an O'Reilly article on the topic and includes a few elements I found necessary to get it all working.
Here's the web method we're going to consume:
[WebMethod (Description="Retrieve the production volume for a given year and region. Returns a single integer.")]
public System.Xml.XmlDocument GetVolume(int year, string region)
{
System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
xd.LoadXml("<root><volume>" + MyNamespace.GetVolume().ToString() + "</volume></root>");
return xd;
} The MyNamespace.GetVolume method returns a single value which I then pad with a descriptive tag (volume) and a root tag (root). Without the root tag I kept seeing a "root element is missing" error from the VB, and figured it meant my resultset was empty, not that it was looking for an actual element named "root." Note to people who write error messages for a living: concise is nice unless you write error messages for a living. Just two more characters and this could have read "<root> element is missing."
This is the code-behind for the button-click event from the Excel spreadsheet:
Public Sub GetVolume()
Dim objVolumeCalc As clsws_VolumeCalc
Set objVolumeCalc = New clsws_VolumeCalc
Dim lngVolume As Long
Dim objRange As Excel.Range
Application.ActiveSheet.Range("A2").Activate
Do
If Application.ActiveCell.Value = "" Then
Exit Do
Else
lngVolume = 0
lngVolume = objVolumeCalc.wsm_GetVolume(Application.ActiveCell.Value, "CAN").Item(0).Text
Set objRange = Application.ActiveCell.Offset(ColumnOffset:=1)
objRange.Value = lngVolume
objRange.Activate
Set objRange = Application.ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-1)
objRange.Activate
End If
Loop
End Sub
The companion article shows how to install and use the Office 2003 Web Services Toolkit to create the web service reference. This creates a proxy class in your VB code-behind to make the web service calls. The next frustrating error I received was "argument not optional." At that time my WS call looked like this:
lngVolume = objVolumeCalc.wsm_GetVolume(Application.ActiveCell.Value, "CAN")
The question being, "which argument is missing?" It was apparently the argument for the item specifier I hadn't yet added:
lngVolume = objVolumeCalc.wsm_GetVolume(Application.ActiveCell.Value, "CAN").Item(0).Text
Happily, the string is auto-basically converted to a long, and getting this line right was the final trick to getting the spreadsheet working. What does the rest do? Well, it uses the values from cell A2 on down to populate the cells from cell B2 on down with the results of the web service calls. This code came from an article that is now otherwise outdated.
The end-result is a form with a button that looks something like this:
