This documentation is archived and is not being maintained.

Fabrikam Account Explorer Template for Excel 2002

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Charles Maxson

July 2002

Applies to:
     Microsoft® Excel 2002

Summary: The Fabrikam Office XP Web Services Integration (OWSI) Sample solution connects users with XML Web service data while working within Microsoft Office applications. The Fabrikam Account Explorer template for Microsoft Excel 2002 (Account Explorer) is a demonstration of how an Excel template can build on top of the Fabrikam OWSI framework to offer users a rich, interactive view of data available from XML Web services. (3 pages)

Usage Overview

The Fabrikam Account Explorer template for Excel 2002 was developed to offer marketing and sales personnel a quickly prepared, easy to use, interactive tool for reviewing and analyzing customer data. The Account Explorer template is simply a traditional Office Excel template that leverages enterprise data that has been exposed as XML Web services. This combination of XML Web services and Excel allows users to benefit from familiar, powerful features such as PivotTable® reports, charts, and the ability to filter data and perform calculations with data that comes from virtually any source, now readily available through open standards.

Another key benefit of building the Account Explorer as an Office document-based application is the offline capabilities inherent to Office documents. In other words, users can use the template and populate it with data while connected, but then take that data and workbook with full functionality and use it in offline scenarios, which is not the case with Web browser-based applications.

To use the Account Explorer template, it simply needs to be opened in Excel. If it is available as a template under the current user's profile (meaning it is located in the Office templates path – commonly C:\Documents and Settings\<user name>\Application Data\Microsoft\Templates) it can be launched using the New menu command (File menu), and then by selecting Fabrikam Account Explorer.xlt, otherwise it can be opened directly as a typical workbook.

When the template opens, its Workbook_Open event calls a series of routines that use the Fabrikam OWSI Client Object Library to retrieve data from Web services. For more information on the Fabrikam OWSI Client Object Library, see the accompanying document Technical Overview of the Fabrikam Office Web Services Integration Sample Solution. Once the template is updated with the current data, the user is free to review and manipulate the data in any way they would like. The template enables users to "explore" one customer account at a time, and the current account being viewed can be changed by selecting the name of another customer from the Account drop down list. The change to the drop down calls a process that once again refreshes the XML Web service data using the Fabrikam OWSI Client Object Library based on the selected account. The user interface of the template, as seen in Figure 1, mimics a browser-like feel to make it very easy for users to navigate. Working with the template beyond that should seem very natural to users since it's really just Excel.

Click here for larger image

Figure 1. Fabrikam Account Explorer template (click thumbnail for larger image).

Technical Overview and Highlights

The Fabrikam Account Explorer Template is a good example of why Microsoft Office is such an advantageous development platform. The template offers users an abundance of features and value with relatively little development effort. In a nutshell, the template uses the Fabrikam OSWI Client Object Library to bring in data from several different XML Web services and then manipulates that data with Microsoft Visual Basic® for Applications (VBA) and common Excel objects such as Range, Worksheet and PivotTable. The majority of the template, however, is actually set up from within Excel and not the Visual Basic Editor, keeping the code base as light as possible.

Retrieving Data

The template retrieves data from the Fabrikam OWSI Client Object Library using the object's InvokeComplexTypeWebMethod method. This method requires several parameters including the name of the XML Web service, the Web method, and an array of the parameters to be passed to the Web method. The template, instead of using parameters coded within VBA, has hidden worksheets within it that store the parameters as well as hold the returning data from the call. The result is the template has one hidden data worksheet for each XML Web service that needs to called, and each call is then executed by using a single function named RefreshDataArray that accepts the worksheet name as a parameter.

This process allows the developer to make a new call for data against the Fabrikam OWSI Client Object Library by simply writing one line of code to call the RefreshDataArray function, and then by adding a new worksheet with defined range names populated with parameter values. The range names as required include:

  • WebService – the name of the XML Web service
  • WebMethod – the name of the Web method
  • HeaderRow – a Boolean value if the data set should include a header row
  • Parameters – an array list of parameters to be passed to the Web method (may include multiple cells)
  • Data – the location where the data should be returned

Once retrieved, the data from these hidden worksheets becomes the source for all the PivotTable reports, charts, and data ranges on the presentation worksheets.

Template Presentation and Navigation

As previously mentioned, the look and feel of the Account Explorer template is patterned to mimic a browser-like user experience. The goal is to make the layout intuitive, crisp, and efficient. Therefore, many Excel features like gridlines, sheet tabs, and row and column headings give way to heavy formatting, graphics, and a Web-like navigation bar. The navigation bar is a simple graphic of "tab buttons" that resembles many Web interfaces. Placed over the top of the tab buttons are Excel text boxes that have no fill or border and bear the tab's name in white text font. The text boxes are assigned hyperlinks that give the simulated feel of browsing and navigating pages, but they are doing no more then simply jumping to another worksheet in the template when followed. (The only exception is the Charting button which required code because Excel chart sheets can't be hyperlink targets.) Finally, on each of the presentation worksheets, a unique navigation bar is repeated to give the impression of an "active tab" and grouped as an Excel group object.