8.2. Currency conversion with a SOAP Web service Ellen would like to extend the XML expense report system for foreign travel and thinks that automatic capture of currency conversion rates would be a requirement. Fortunately, there is a Web service available for the purpose. We will create a simple worksheet to develop and test the use of the Web service. Eventually, we can move the code to the expense report worksheet. For pedagogical reasons we will use the SOAP interface to the service (it has REST interfaces as well) and we will again use the Office Web Services Toolkit to hide the complexity of SOAP (as we did in 6.2, "SOAP Web services", on page 121). Also for pedagogical reasons we will use code to update individual cells instead of simply creating an XML map and importing the Web service output. Using code will allow us to see how the toolkit returns the data.[1] [1] In real life we would only use code if we needed more control over the placement of the data in the worksheet; for example, for complex structures that we needed to flatten or rearrange in some way. The worksheet is shown in Figure 8-5. It has cells for the parameters – the "from" currency ("Expense Currency") and the "to" currency ("Expense Report Currency") – and a Refresh button that retrieves the exchange rate from the Web service. Figure 8-5. The currency converter worksheet 
To create this example, open the worksheet currency converter start.xls. It is a skeleton worksheet to which we will add the currency lookup capability. 8.2.1 The currency converter Web service The Web service is located at: http://www.webservicex.net/CurrencyConvertor.asmx. It has one operation, named ConversionRate, that will provide the current exchange rate, given a "from" currency and a "to" currency. If you browse the above URL, you will see a brief description of the operation. Clicking on ConversionRate will bring up a page that describes the operation fully and provides examples. Although the page shows the input and output messages, we do not need to concern ourselves with them because of our use of the Office Web Services Toolkit. 8.2.2 The Office Web Services Toolkit Generating the necessary classes in Excel is identical to the process described in 6.2.3, "Using the Web Services Reference Tool", on page 126. We execute that process, this time using the URL: http://www.webservicex.net/CurrencyConvertor.asmx?wsdl The name of the Web service and its methods is shown on the right side of the dialog in Figure 8-6. Figure 8-6. The Web Services Reference Tool 
The procedure generates a single class named clsws_CurrencyConvertor which allows access to the ConversionRate operation. 8.2.3 Refreshing the data As in the previous example, we will use a Refresh button to update the data in the worksheet. Follow the instructions in 8.1.3, "Refreshing the data", on page 167 to create the Refresh button. This time, use the code shown in Example 8-5, which can be copied from the example file refresh_currency.txt. Example 8-5. Refresh code for symbol lookup example Private Sub Refresh_Click() Dim objLookup As New clsws_CurrencyConvertor Dim fromCurrency As String Dim toCurrency As String Dim rate As Double fromCurrency = Range("B5").Text toCurrency = Range("B6").Text Set rateRange = Range("B8") rate = objLookup.wsm_ConversionRate(fromCurrency, toCurrency) If (rate) Then rateRange.Value = rate Else rateRange.Value = 0 End If End Sub
-
Line 3 creates a new instance of the generated class clsws_CurrencyConvertor. -
Lines 5 through 7 declare the other variables used. -
Lines 8 and 9 assign the text entered by the user in cells B5 and B6 to the variables fromCurrency and toCurrency. -
Line 10 sets the range to be filled in, namely cell B8. -
Line 12 calls the method wsm_ConversionRate and assigns the result to the variable rate. If rate is not null, the value is assigned to the appropriate cell. Note that, unlike the previous example, there is no XML Map involved. The data returned from the Web service is placed into the cells by the code, rather than through XML import. This technique allows more control over which data is mapped and where it is placed in the worksheet. A complete working version of this example can be found in the sample file currency converter.xls. | 
