Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Web Services

Like XML, Web services is another term you've probably heard about with mild curiosity, but ultimately rejected as being irrelevant to Excel. This section aims to explain what Web services are, how to create them (using Visual Basic.NET) and how they can play an important role in our applications. What we will not do is explain how they work, because that is largely irrelevant to us as Excel developers.Practical Example section, we modify the PETRAS timesheet add-in to retrieve data from and send data to a Web service. Instead of the timesheet add-in connecting directly to a database on the network, the Web service will handle the database connection. In both cases, we focus on using the Web service, rather than creating one with all the scalability and security considerations. For this example we use a local Web server (http://localhost) to run the Web service. To run the examples in this section, you will need a computer running Internet Information Services and have Visual Studio.NET 2003 to create the Web service. The Excel part of the example works in any version from Excel 2000 forward.

Creating a Web Service with VB.NET

To demonstrate how to connect to and use a Web service from Excel, we'll create a very simple one to reproduce the AddTwo and MultiplyTwo functions seen in Chapter 19 XLLs and the C API. Start Visual Studio.NET 2003, start a new project, choose the ASP.NET Web Service Visual Basic project and rename the location to http://localhost/ProExcelDev, as shown in Figure 23-11.

Figure 23-11. Creating a New Visual Basic Web Service

[View full size image]

Click OK to let Visual Studio create a new Web service project. This project includes a class called Service1 that we want to rename to be Maths. The name needs to be changed in both the filename (by editing the filename in the Solution Explorer tree) and the class name (by editing the code module). In Listing 23-11 we've changed the class name in both lines three and four and added the two functions we want to make available to users of our Web service.

Listing 23-11. The ProExcelDev Maths Web Service

'The Professional Excel Development Maths Web Service
Imports System.Web.Services
<System.Web.Services.WebService( _
Namespace:="http://tempuri.org/ProExcelDev/Maths", _
Description:="Pro Excel Dev Maths Functions")> _
Public Class Maths
Inherits System.Web.Services.WebService
[Web Services Designer Generated Code]
'Add two numbers
<WebMethod(Description:="Adds two numbers")> _
Public Function AddTwo(ByVal d1 As Double, _
ByVal d2 As Double) As Double
Return d1 + d2
End Function
'Multiply two numbers
<WebMethod(Description:="Multiplies two numbers")> _
Public Function MultiplyTwo(ByVal d1 As Double, _
ByVal d2 As Double) As Double
Return d1 * d2
End Function
End Class

That's all there is to it; we've created a Web service! The key bit is the <WebMethod()> attribute that we add to any functions we want to expose. In this example, we're only passing simple data typesdoublesbut in the PETRAS Web service, we'll be passing and returning more complex data sets, using XML. Build the solution, close Visual Studio and let's get on with the interesting bitusing the Web service from Excel.

Using a Web Service

Excel's Web Service connectivity is provided by the Microsoft Office Soap Type Library, mssoap30.dll, included in the Office Web Services Toolkit. The toolkit is an optional install in Office 2003 Professional and can be down loaded by following the Office 2003: Web Services Toolkit2.01 link from [ http://msdn.microsoft.com/office/downloads/toolsutils/default.aspx ]. When deploying applications that use Web services, our users will also need to have the Web Services Toolkit installed, to provide them with the mssoap30 DLL and its dependencies. As well as containing the type library, the toolkit includes an add-in to the VBIDE that enables us to find and select Web services, then adds classes to our VBProjects to wrap the calls into the Soap Type Library and expose the Web service as a standard VBA class (or set of classes). Despite its name, the Web Services Toolkit is not dependent on Excel 2003 and works fine in all versions from Excel 2000 forward.

Download and install the toolkit, switch to the Excel VBE and click on Tools > Web Service References… to bring up the Microsoft Office Web Services Toolkit dialog. This dialog provides the capability to search for a Web service by keyword (by linking to a Microsoft Web site for that information), but we'll provide it with the location of the ProExcelDev Maths Web service we created above. When we built the Web service, Visual Studio compiled our source code into a file called Maths.asmx, which is the Web service equivalent of an EXE or DLL. Because we know which file to connect to, we can tell the Web Services Toolkit to connect directly to it and search for the Web services it contains, as shown in Figure 23-12.

Figure 23-12. Connecting to the ProExcelDev Maths Web Service

[View full size image]

When we click the Add button, the toolkit will create class modules for each of the Web services we've ticked in the top-right box. So tick the Maths Web service, click Add and look at the generated code in the new clsws_Maths class the toolkit just created. An extract of the generated code is shown in Listing 23-12 (where we've removed the error handling and changed a few comments for clarity).

Listing 23-12. The Generated Class to Connect to the Maths Web Service

'Dimensioning private class variables.
'The sc_Maths object handles all the communication
Private sc_Maths As SoapClient30
'These constants reflect the selections in the dialog,
'and tell the class where to connect to
Private Const c_WSDL_URL As String = _
"http://localhost/ProExcelDev/Maths.asmx?wsdl"
Private Const c_SERVICE As String = "Maths"
Private Const c_PORT As String = "MathsSoap"
Private Const c_SERVICE_NAMESPACE As String = _
"http://tempuri.org/ProExcelDev/Maths"
Private Sub Class_Initialize()
Dim str_WSML As String
str_WSML = "
Set sc_Maths = New SoapClient30
'Initialize the connection to the Web service
sc_Maths.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, _
c_PORT, c_SERVICE_NAMESPACE
sc_Maths.ConnectorProperty("ProxyServer") = "<CURRENT_USER>"
sc_Maths.ConnectorProperty("EnableAutoProxy") = True
End Sub
Private Sub Class_Terminate()
Set sc_Maths = Nothing
End Sub
'Wrapper for the AddTwo function in our Web service
Public Function wsm_AddTwo(ByVal dbl_d1 As Double, _
ByVal dbl_d2 As Double) As Double
wsm_AddTwo = sc_Maths.AddTwo(dbl_d1, dbl_d2)
End Function
'Wrapper for the MultipleTwo function in our Web service
Public Function wsm_MultiplyTwo(ByVal dbl_d1 As Double, _
ByVal dbl_d2 As Double) As Double
wsm_MultiplyTwo = sc_Maths.MultiplyTwo(dbl_d1, dbl_d2)
End Function

The class module is generated from the selections we made in the Web Services Toolkit dialog. The class uses a module-level variable called sc_Maths to hold a reference to a SoapClient30 object, which does all the communication with the Web service for us. The constants at the top of the module specify the location of the Web service, its name and so on, which are used in the Class_Initialize event to connect to the service. The rest of the class contains wrappers for each function exposed by our Web service, each of them having the prefix wsm_, for Web service method. As this is just a normal class module, we can, of course, change the names to anything we want, add more properties, and so forth.

To use the Web service from our VB code, we create an instance of the class and call the wrapper functions, as shown in Listing 23-13.

Listing 23-13. Using the Maths Web Service

Sub Add1And2()
Dim clsMaths As clsws_Maths
Set clsMaths = New clsws_Maths
MsgBox "1 + 2 = " & clsMaths.wsm_AddTwo(1, 2)
End Sub

We could, of course, put the same code in a standard VBA user-defined function and call it from the worksheet, so if, say, a proprietary pricing function has been exposed as a Web service, we can now use it within our worksheets!