Excel

Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services

Alok Mehta

Code download available at:ExcelWebServices.exe(251 KB)

Contents

The Toolkit
An Academic Scenario
ASP.NET Web Services Server Setup
VBA Client Setup
Web Service Security
Wrap-Up

Microsoft® Office 2003 contains powerful XML-based functionality, including Web services integration. For example, using the Web services capabilities of Office 2003, developers can create a variety of solutions that help users increase their productivity on eBay. Web services offered by eBay allow you to use Excel to manage dynamic listing data, such as pricing and bids, and update this data automatically as changes occur. Solutions could also enable users to list multiple items on eBay simultaneously or to create a Web site that displays their eBay item listings.

This is all made possible with the Office 2003 Web Services Toolkit, which minimizes the programming required to integrate data returned from Web services with Visual Basic® for Applications (VBA). Office 2003 and Web services together offer a platform for building robust solutions to manage useful applications. You can download this tool from Microsoft Office 2003 Web Services Toolkit 2.01.

Among the applications within Office, Excel 2003 in particular allows developers to dynamically integrate the data provided by Web services. It also allows you to take advantage of the latest capabilities in Office 2003 to customize list views, graphs, and charts, and to catalog bulk items online or offline. The Web service integration within Excel 2003 can also provide ad hoc data analysis in the familiar environment of Excel, such as statistical analysis of the data extracted from the Web service.

This article describes how the Office 2003 Web Services Toolkit provides an API that allows you to select the Web services you want and create proxy classes for your VBA. The tool is accessible from the Web Service References command on the Tools menu in the Office Visual Basic Editor after the toolkit has been installed. By using the Office Visual Basic Editor, programmers can now set references to XML Web services just as they would any intrinsic type library. The VBA proxy classes generated by the tool allow both early binding and IntelliSense®, which makes consuming XML Web services easier and more intuitive, and virtually eliminates syntax errors.

The Toolkit

The Office 2003 Web Services Toolkit allows you to quickly access and use Web services with VBA from Office 2003 applications. It allows you to use a GUI to either search for Web services from any Universal Description Discovery and Integration (UDDI) registry or to specify a particular Web service by providing a URL to the file that contains the Web service description. The target file can be in Web Services Description Language (WSDL) format or the Visual Studio® Discovery (VSDisco) format, which is still supported even though it's obsolete. In case the description file contains information on multiple Web services, the GUI allows the user to select either some or all of the Web services for use within their current document.

The tool then generates the code for classes that access the Web services using the Microsoft SOAP client object. These generated classes will have methods corresponding to the operations supported by the Web services. The user can write simple VBA scripts without having to know that the objects she is using are really proxies to Web services. The toolkit supports Office SOAP 3.0 and requires MSXML 3.0.

The Office 2003 Web Services Toolkit also includes the Web Services References Tool, which allows you to locate Web services. The best part of this toolkit is that it automatically generates code for you. I'll discuss the automatic code generation listings shortly.

An Academic Scenario

To illustrate the power of using ASP.NET Web services in VBA, I have developed a simple example that includes both a sample Web service as well as a spreadsheet that uses it. The purpose of the Web service is to retrieve students' grades for a given professor and semester. This example uses a database with a simple data model (see Figure 1). The general idea is as follows: a professor teaches a course with many students in a given semester. Students are graded on quizzes, homework, a mid-term exam, class participation, and a final exam. Many times, professors want to analyze student performance in a particular semester, looking at a student's average grades and other statistics. By exposing this information through a Web service, professors could consume that data in Excel 2003 and use it to calculate a variety of interesting statistics.

Figure 1** Student Grades Data Model **

The sample is divided into the following three parts: the server-based ASP.NET Web service, called Get_Grades; the client-side VBA in a spreadsheet, called Client.XLS, which was developed in Excel 2003; and the toolkit code that runs on the client side. This code is automatically generated by the toolkit and provides a proxy to the ASP.NET Web service by wrapping calls to it.

ASP.NET Web Services Server Setup

To create the Get_Grades Web service, you first need to create a Web service in ASP.NET and add the code from Figure 2. Start Visual Studio .NET and open a new ASP.NET Web service project. Name the Web service project Get_Grades. If your machine has IIS installed, ASP.NET will automatically create the Web service for it. In addition, ASP.NET creates several files. Among the most important is Service1.asmx.vb, which will contain the public method Get_Grades available via the Web services. Simply cut and paste the source code in Figure 2 into Service1.asmx.vb. I'll describe the Web service source code in the next section. For now, let's work on building the solution.

Figure 2 Sample Web Service

'Alok Mehta 'February 2005 'MSDN VBA WebService Example Option Explicit On Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Services <WebService(Namespace:="https://myCollege.edu/")> _ Public Class Service Inherits System.Web.Services.WebService 'Provide grades for all homework, exams, and so on. Based on 'professor and course <WebMethod()> Public Function Get_Grades( _ ByVal nProfessorID As Long, ByVal lngCourseID As Long) As String 'Make sure its a valid request If bValidUser(nProfessorID, lngCourseID) Then 'Dataset to store the results Dim GradesDataSet As DataSet = New DataSet() 'Make connection to the Grades database; the connection 'string would be in a config file in real application Dim GradesConn As SqlConnection = New SqlConnection( _ "Data Source=localhost;Integrated Security=SSPI;" & _ "Initial Catalog=Grades") Try 'SQL to get the students grades for a particular 'professor and course Dim strSQL As String strSQL = "SELECT tbl_Student.Student_Name, " & _ "tbl_Grades.Quiz_Grade, tbl_Grades.HomeWork, " & _ "tbl_Grades.MidTerm, tbl_Grades.Participation, " & _ "tbl_Grades.FinalExam FROM tbl_Student INNER JOIN " & _ "(tbl_Professor INNER JOIN (tbl_Course INNER JOIN " & _ "tbl_Grades ON " & _ "tbl_Course.Course_ID=tbl_Grades.Course_ID) ON " & _ "tbl_Professor.Professor_ID=tbl_Grades.Professor_ID) " &_ "ON tbl_Student.Student_ID=tbl_Grades.Student_ID " & _ "WHERE tbl_Professor.Professor_ID=@profID AND " & _ "tbl_Course.Course_ID=@courseID" 'Create the command to execute Dim Comm as SqlCommand = New SqlCommand(strSQL, GradesConn) Comm.Parameters.Add("@profID", nProfessorID) Comm.Parameters.Add("@courseID", lngCourseID) 'Use a data adapter to fill the DataSet, then get as XML Dim dataGrades As SqlDataAdapter = New SqlDataAdapter(Comm) dataGrades.Fill(GradesDataSet) Get_Grades = GradesDataSet.GetXml Catch e As Exception 'Return any error messages Get_Grades = e.Message Finally GradesConn.Close() End Try Else 'In case its not a valid user Get_Grades = "Invalid user" End If End Function Private Function bValidUser(ByVal nProfessorid, ByVal lngCourseid) _ As Boolean 'This function should be replaced by your security standards 'such as a Web service token etc. For simplicity we just allow 'a specific professor ID and course ID If nProfessorid = 1 And lngCourseid = 1 Then bValidUser = True Else bValidUser = False End If End Function End Class

VBA Client Setup

To set up Client.XLS on the developer's machine, copy it from the code download to any folder you prefer. In Excel, use the Visual Basic Editor to look at the code attached to the XLS file. You should see cmdGetDataFromWebService_Click within the WebServiceClient Sheet object. The code for this is shown in Figure 3. The class module, clsws_Get_Grades, is shown in Figure 4.

Figure 4 Auto-Generated Client Proxy

'***************************************************************** 'This class was created by the Microsoft Office 2003 Web Services Toolkit. ' 'Description: 'This class is a Visual Basic for Applications class representation of the 'Web service as defined by https://localhost/grades/WebService.asmx?wsdl. ' 'To Use: 'Dimension a variable as new clsws_Service, and then write code to 'use the methods provided by the class. 'Example: ' Dim ExampleVar as New clsws_Service ' debug.print ExampleVar.wsm_Get_Grades("Sample Input") ' 'For more information, see Complex Types in Microsoft Office 2003 'Web Services Toolkit Help. ' 'Changes to the code in this class may result in incorrect behavior. ' '***************************************************************** 'Dimensioning private class variables. Private sc_Service As SoapClient30 Private Const c_WSDL_URL As String = _ "https://localhost/grades/WebService.asmx?wsdl" Private Const c_SERVICE As String = "Service" Private Const c_PORT As String = "ServiceSoap" Private Const c_SERVICE_NAMESPACE As String = "https://myCollege.edu/" Private Sub Class_Initialize() '***************************************************************** 'This subroutine will be called each time the class is instantiated. 'Creates sc_ComplexTypes as new SoapClient30, and then 'initializes sc_ComplexTypes.mssoapinit2 with WSDL file found in 'https://localhost/grades/WebService.asmx?wsdl. '***************************************************************** Dim str_WSML As String str_WSML = "" Set sc_Service = New SoapClient30 sc_Service.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, _ c_SERVICE_NAMESPACE 'Use the proxy server defined in Internet Explorer's LAN settings by 'setting ProxyServer to <CURRENT_USER> sc_Service.ConnectorProperty("ProxyServer") = "<CURRENT_USER>" 'Autodetect proxy settings if Internet Explorer is set to autodetect 'by setting EnableAutoProxy to True sc_Service.ConnectorProperty("EnableAutoProxy") = True End Sub Private Sub Class_Terminate() '***************************************************************** 'This subroutine will be called each time the class is destructed. 'Sets sc_ComplexTypes to Nothing. '***************************************************************** 'Error Trap On Error GoTo Class_TerminateTrap Set sc_Service = Nothing Exit Sub Class_TerminateTrap: ServiceErrorHandler ("Class_Terminate") End Sub Private Sub ServiceErrorHandler(str_Function As String) '***************************************************************** 'This subroutine is the class error handler. It can be called from any 'class subroutine or function when that subroutine or function 'encounters an error. Then, it will raise the error along with the 'name of the calling subroutine or function. '***************************************************************** 'SOAP Error If sc_Service.FaultCode <> "" Then Err.Raise vbObjectError, str_Function, sc_Service.FaultString 'Non SOAP Error Else Err.Raise Err.Number, str_Function, Err.Description End If End Sub Public Function wsm_Get_Grades(ByVal dcml_nProfessorID As Double, _ ByVal dcml_lngCourseID As Double) As String '***************************************************************** 'Proxy function created from 'https://localhost/grades/WebService.asmx?wsdl. ' '"wsm_Get_Grades" is defined as XML. See Complex Types: XML Variables 'in Microsoft Office 2003 Web Services Toolkit Help for details on 'implementing XML variables. '***************************************************************** 'Error Trap On Error GoTo wsm_Get_GradesTrap wsm_Get_Grades = sc_Service.Get_Grades(dcml_nProfessorID, _ dcml_lngCourseID) Exit Function wsm_Get_GradesTrap: ServiceErrorHandler "wsm_Get_Grades" End Function

Figure 3 Client-Side VBA

'MSDN VBA Excel-WebService Client Example Code Option Explicit 'WebService Dataset will be temporarily saved in the following XML file Const XMLFileName = "C:\Grades.XML" 'Professor will be given the following two IDs by the IT department. Const Course_ID = 1 Const Professor_ID = 1 Private Sub cmdGetDataFromWebService_Click() On Error GoTo Error_Processing Dim objGrades As New clsws_Service 'Instantiate the Web service proxy Dim strReturnValue As String 'XML string from the Web service Dim bSaveFile As Boolean 'Saved the XML file Dim intI As Integer 'Local Counters Dim intJ As Integer Dim intOffset As Integer intOffset = 8 'Starting Row Dim strColumn As String 'Spreadsheet Columns Dim strValue As String 'Values to be copied into cells Dim strFieldName As String 'XML fields mapped to the db fields Dim oRoot As MSXML2.IXMLDOMNode 'XML files's root Dim NewXMLdocument As New MSXML2.DOMDocument 'XML from the service 'Get data from Web service strReturnValue = objGrades.wsm_Get_Grades(Professor_ID, Course_ID) bSaveFile = SaveToXML(XMLFileName, strReturnValue) If bSaveFile Then 'Load the data from the file and iterate over the DOM Call NewXMLdocument.Load(XMLFileName) Set oRoot = NewXMLdocument.documentElement For intI = 0 To oRoot.childNodes.Length - 1 For intJ = 0 To oRoot.childNodes.Item(intI).childNodes.Length 1 'Get the value and field name for each node strValue = oRoot.childNodes.Item(intI) _ .childNodes.Item(intJ).Text strFieldName = oRoot.childNodes.Item(intI) _ .childNodes.Item(intJ).baseName 'Map the database fieldanme to the spreadsheet cells Select Case strFieldName Case "Student_Name": strColumn = "A" Case "Quiz_Grade": strColumn = "C" Case "HomeWork": strColumn = "D" Case "MidTerm": strColumn = "E" Case "Participation": strColumn = "F" Case "FinalExam": strColumn = "G" End Select 'Set the cell's value based on the XML data ActiveSheet.Cells(intI+intOffset,strColumn).Value=strValue 'Keep track of what's happening to aid in debugging Debug.Print strFieldName, strValue Next Next MsgBox "Web Service Data Get was successful" End If Set objGrades = Nothing 'Release the object Exit Sub Error_Processing: MsgBox "Error in Web Service Data Get " & Err.Description End Sub Private Function SaveToXML(strValue As String, strFileName As String) _ As Boolean On Error GoTo Error_Processing 'Saves a string value into a text file using FileSystemObject Dim fs As Object Dim a Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile(strValue, True) a.WriteLine (strFileName) a.Close SaveToXML = True Exit Function Error_Processing: MsgBox "Cannot Save XML File " & Err.Description SaveToXML = False End Function

Assuming that you have installed the toolkit, going to Tools | Web References in the editor should show you the screen that you can see in Figure 5.

Figure 5** Office 2003 Web Services Toolkit in Excel **

Open the Office Visual Basic Editor in any Office application that supports VBA. Set a reference to the Microsoft Soap Type Library (MSSOAP3.DLL) from the References dialog box (located in the Tools menu). Then, on the Tools menu, choose the Web Service References option.

One of the important items in the toolkit is a Web service search, which lets you search against a UDDI Business Registry using either keywords or a business name. (We will look at UDDI later.) The Web Service URL feature lets you perform an XML Web service search (including wildcards) using a specific URL to a WSDL or Visual Studio .NET Discovery (.vsdisco) file. The Search Results page then allows you to select available XML Web services for which you want the tool to create proxy classes. Finally, the test button allows you to test an XML Web method you select from Search Results (if the XML Web service administrator has provided a corresponding .asmx test harness page).

Once you select one or more Web services for which you want the tool to create proxy classes and click the Add button, the toolkit will create one class module (.cls) file for each selected Web service. Each .cls file will begin with the prefix clsws_ followed by the Web service's name. For instance, if I add a Web service to my project with the name Get_Grades, the class name will be clsws_Get_Grades.

Now, let's go back to the code for the Web service itself. Shown in Figure 2, this code exposes a public Web method called Get_Grades that accepts two parameters: nProfessorID and lngCourseID. Its purpose is to return grades of a particular professor and course in the form of an XML string. Figure 6 shows the Get Grades button.

Figure 6** Get Grades Button **

The example Web service uses the following four .NET namespaces: System, System.Data, System.SqlClient, and System.WebServices. You can get more information about these namespaces in the MSDN Library.

Since this system is designed for professors only, the bValidUser function was created to validate the professor ID that is being passed to the Web service. Likewise, each semester is identified using a semester ID. The code on the client sets and passes these IDs to the Web service to extract the relevant data from the database so that an analysis can be performed in the client Excel file or application. The bValidUser function can be enhanced to include more complex validation and verification logic by implementing signed security tokens such as X.509 and Kerberos tickets as explained in Security in a Web Services World: A Proposed Architecture and Roadmap. You can also enhance bValidateUser to pull data from your own database to map the professor and course IDs. However, for the purposes of this example, I have simply hardcoded the IDs to be 1 so we can focus on power of Web services and Excel.

I made use of ADO.NET DataSets and their connection properties to extract the data from the SQL Server™ database. GradesConn is the SQL Server connection that defines the connection to the Grades database. GradesDataSet is populated from the database, and the DataSet is then converted to an XML string and is ultimately returned using the GradesDataSet.GetXml property.

Figure 3 is the client-side VBA code. The bulk of the code is contained in the cmdGetDataFromWebService_Click event handler. The purpose of this routine is to get the data in the form of an XML string from the Web service and parse it into the specified cells. The data is retrieved by calling objGrades.wsm_Get_Grades(Professor_ID, Course_ID). Recall that Professor_ID and Course_ID are constants and are assumed to be provided by the IT department of the college. Once the XML string is returned from the Web service, it is saved to disk so you can deserialize the data into a DOM now or at a later date.

Now that you have a valid XML file, called c:\Grades.XML, you can iterate through all of its nodes and their children. As I iterate through each of the children, I can extract the values and field names using oRoot.childNodes.Item(intI).childNodes.Item(intJ).Text and oRoot.childNodes.Item(intI).childNodes.Item(intJ).baseName, respectively. The spreadsheet columns are then filled using ActiveSheet.Cells(intI + intOffset, strColumn).Value, where the row in the active sheet is set by intI + intOffset and strColumn is based on strFieldName. Once the code in Figure 3 is executed, you should see results, as shown in Figure 7.

Figure 7** Data Retrieved from Web Services in Excel 2003 **

Now that we have extracted the data from the server, let's explore Client.XLS. Columns A through G are filled by the VBA code I just discussed. Column I presents the automatically calculated, weighted average of the assessment. It represents the final grade that the professor wants to see.

Since Excel provides an excellent utility for creating statistical functions and charts, I decided to use those functions to convert the data extracted from the Web service into useful information. This information is contained in the Information tab of Client.XLS and is shown in Figure 8. These four useful sets of information are marked as A, B, C, and D. Part A contains simple mean, median, and mode. Part B contains frequency (within 20 points) of the final grades. Parts C and D are graphical representations of the frequency and student grades.

Figure 8 Using Excel and Web Service Data

The code in Figure 4 is automatically created by the toolkit. It includes a VBA class representation of the Web service as defined by https://localhost/grades/WebService.asmx?wsdl. This code is created by the Web service component that comes with Excel 2003, and modifying it may result in unexpected behavior—so don't make any changes to it. The most important function in this class is wsm_Get_Grades, which is the proxy function created from https://localhost/grades/WebService.asmx?wsdl. The function wsm_Get_Grades is defined to return XML data.

Web Service Security

A discussion of Web services security is outside the scope of this article, but it is a very important issue. The MSDN Library hosts several articles that discuss security principles in Web services, such as Security in a Web Services World: A Proposed Architecture and Roadmap. Also, the Patterns & Practices team at Microsoft has published several articles on Web service security, such as Web Services Security. You should also consider issuing signed security tokens such as X.509 and Kerberos tickets to your VBA clients for securing your Web service.

Wrap-Up

In this article, I've demonstrated how to use a powerful and familiar tool, Excel, to access XML data from a Web service using VBA. A stateless Web service can wrap complex business logic in a black box that can then be called using Excel 2003 as a client.

Although I only discussed the Web service and client that are on the same machine, in real applications the Web services will often be hosted on a different server. You can certainly publish your Web services on your own enterprise Web servers and have your users use the Excel VBA client to access the Web service API. Additionally, you can register your Web services on UDDI where other businesses can know about your presence and the Web service APIs. UDDI is a comprehensive industry initiative enabling businesses to define their business, discover other businesses, and share information about how they interact in a global registry. UDDI is the building block that enables businesses to quickly, easily, and dynamically find and transact with one another via VBA. The UDDI standard takes advantage of W3C standards such as XML, HTTP, and DNS protocols to provide service description and discovery. UDDI also addresses cross-platform programming features.

Alok Mehta is senior vice president and chief technology officer of AFS Technologies in Weston, Massachusetts. He recently completed his Ph.D. in Computer Science from Worcester Polytechnic Institute.