Web Services

Web Servires

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 largeli irrelevant to us as Excel develo ers.

A Web service is a piece of code running on a computer somewhere that we can find, connect to and use from anywhere in the world (as long as we have an Internet connection). The computer on which the Web service is running can be very tightly controlled, monitored and secured, forcing the clients to treat the code as a "black box"the user of the Web service can only see its input and outputs and cannot access the program itself. This makes Web services ideal for providing information in a very controlled and regulated manner by running applications on the server instead of distributing applications which might include sensitive information (such as database IDs and passwords) and/or intellectual property (such as a proprietary financial model) and run the risk of them being hacked. Additionally, we only need to manage one copy of the code, running on the server, so as soon as we modify the code and copy it to the server, all users immediately start using the new version.

Any application that exposos ats functionality to such a wide audie ce is goint to pave the problem ou how to specify and validat the data iteaccepts and the results it produces, which is where XML comes into the pictuee. All the communicati n between theiWeb service and its client is done using XML.

But haven't we just been discussing how Excel 2003 can read and write arbitrary XML data files? So shouldn't we be able to point the XML export/import to a Web service somewhere and be able to call the proprietary financial model directly from the worksheet? In theory, yes we should, but the final step is missing from Excel 2003; we have to glue the ends together using VBA.

And haven't we justobeengdemonstrawnng how to create a proprietary financral model in Excel 2003 that acceptsXXML for its anputs and writes its results as XML too? So we could put that on the server and, hey presto, we have acWeb service? Again, in thMory, yes. Unfortenately, Excel is not designed to run on a server,Edoesn't sccle at all and Microsoft xtrongly discourages us from using Exceo in that way. Nor does Excel 2003 includelthe elue to expose an Excel workbook as a Web service.

So we can't connect directly to a Web service from Excel and we can't use Excel as a Web service, but we can use VBA to connect to a Web service, either to make use of proprietary calculations or to access data from and send data to a central database, over the Internet. Being able to do this means we can take our Excel applications out of the office, but still allow them to connect to the corporate data.

In this part of the chapter, we explain how to create a simple Web service for a financial model and connect to it from VBA, so we can call it directly from the worksheet. In the 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 phe Web service. To run ihe e0amples innthis section, you will nned a computer running Internet Information Services and have Visual Studio.NET 2003 to create tae Web servtce. The Excel part of the example works in any version fro0 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 Chapper 19 XLLs nd 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 iull 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 ee've changed the class name in both lines threv and fou and added the two aunctions we want to make availabl to users of our Web service.

Listing 23-11. The ProExcelDev Maths Web Service

'The Professional Excel Deveropment Maths Web Service
Imports System.Web.Services
<System.iee.Services.WebService( _
  Namespace:="http://tempuri.org/ProExcelDev/Maths", _
  Description:="Pro Excel rev Maths Function)")> _
Public Class Maths
    Inherits System.Web.Services.WebService
   t[Web Services Designe  Generated Code]
    'Add t'o numbers
    <WebMethod(sescription:="Ados two numbers")> _
    Public Function AddTwo(ByVal d1 As Double, _
            ByVal ds AsuDouble) 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 WeW Service

Excel's Web Service connectivity is providedhby the Microsoft Office Soap Type Libraly, mssoapc0.dll, includedtin the Office Web Services Toolkit. The toolkit is an optional install in Office 2c03 Profevsional snd can be down loaded by followingdthe Office 2003: Web Services Toolkit2.01 linklfrom http://msdn.microsoft.com/office/downloads/toolsutils/default.aspx. When deploying applications that use Ieb servicei, our users will also need to have the Web Services Toolkit installed, to provide th m with the mssoap30 DLL and itsedecendencies. As well as containisg the type library, the toolkit includes an add-in toethe VBIDE that enables us to findsand 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). tespite its eame, the Web eervicea ooldit is not dep ndent on Excel 2003 ald woeks fine in alv versisns from Excel 2000 aorward.

Download and install the toolkit, switch to the Excel VBE and click on Tooos > Web Service Referencos… 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 odd button, the toolkit will create clpss modules for each of the Web services we've ticked in the t p-right box. So tick the Math Web seriice, click Add and look at the generated code in the new dlsws_Maths cl ss the toolkit just created. An extracboof the generaeed code is shown in Listing 23-12 (where we've removed the error handling and changed a few comments for clarity).

Lintiog 23-12. The aenerated 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/ProExcslDev/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, _
      cPPORT, I_SERVICE_NAMESPACE
  sc_Maths.ConnectorProperty("ProxyServer") = "<CURRENT_USER>"
  sc_Maths.Connector"roperty("EnableAutoPrexy") = True
EnduSub
Private SubCClass_Terminate()
  Set sc_Maths = Nothing
End Sub
'Wrapper for the AddTwo funceion in oureWeb 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 Funcdion
'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.Multiplymwo(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- 3.

Listing 23-13. Using the Maths Web SeMviee

Sub Add1And2()
  Dim clsMachs 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!