Access 2002 Enterprise Developer's Handbook

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.

 

Chapter 12: Using the Office Web Components

Paul Litwin, Ken Getz, and Mike Gunderloy

December 2002

Applies to:
    Microsoft® Access® 2002

Buy this book

Summary: This article presents an excerpt from the book Access 2002 Enterprise Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gunderloy. Learn how to create simple pages using the Office Web Components and how to use properities, methods and events to script the Web Components. (56 printed pages)

Contents

What Do You Get?
The Office Spreadsheet Component
The Office PivotTable Component
Persisting PivotTable Settings
The Office Chart Component
Summary

• Introducing the Office Web Components
• Creating simple pages using the Office Web Components
• Using properties, methods, and events to script the Web Components
• Investigating the Spreadsheet, Chart, and PivotTable List Components

Ever since developers began creating applications hosted by Microsoft® Office, those developers (including the authors of this book) have wanted a way to easily include functionality that wasn't a part of the application's "base" product. For example, Access developers have long wanted to insert attractive, easily programmed charts into their applications, yet the Microsoft Graph application was, and continues to be, a poorly documented memory hog. Developers have needed to include spreadsheet-like functionality in their applications, but loading a full copy of Excel just to get that functionality seemed like an onerous price to pay. The same goes for the pivot table functionality that Excel provides: you can insert a pivot table into an Access form, but you're loading all of Excel just to work with that pivot table.

With Office 2000, Microsoft introduced a set of controls/components to address these issues. The components, together named the Office Web Components (OWC), can be used in a data access page, on an Access form, on a standard web page, or anywhere else COM components are supported. The Office Web Components include an Excel-like spreadsheet, pivot table, and chart. In addition, the OWC includes the Data Source Control, which provides not only data for these components, but for Access data access pages, as well. (For more information on data access pages, see Chapter 10, Creating Data Access Pages.)

This chapter introduces these components. We intend to discuss each component and how you might use that component. The chapter database, ch12.mdb, includes some instructive examples, both forms and data access pages, and includes code (VBA and VBScript) demonstrating how to manipulate these components. This chapter is not, however, a reference for developing with the Office Web Components. That would require an entire book of its own. We hope you'll find enough material here to get you started experimenting with the Office Web Components.

If you haven't done so already, we suggest you take the time to work through Chapter 10, which covers data access pages, and Chapter 11, Scripting Data Access Pages, which covers scripting, before using the material in this chapter. All of the examples in this chapter assume that you're comfortable with both data access pages and scripting, using VBScript.

What Do You Get?

The Office Web Components include four distinct COM components. The following sections introduce each of the Office Web Components.

Office Spreadsheet

This Excel-like spreadsheet control allows you to create grid-based data entry forms, with immediate recalculation. You can also bind data in cells to other controls on a web page. This control looks and behaves like a single Excel workbook (including, in the Office XP release, multiple sheets), uses the same cell-referencing techniques, and allows you to use almost all the standard Excel spreadsheet functions. You can easily link Office Spreadsheet and Chart controls together, as shown in Figure 12.1.

Figure 12.1. The Office Web Components work together, making it simple to bind a chart to live spreadsheet data (click picture for larger image).

Because of the way this control (and the other Office Web Components) has been created, you needn't host it on a form in order for it to be useful. You can use the powerful Excel calculation engine in your applications without actually creating the Office Spreadsheet user interface anywhere at all. That is, you can invisibly instantiate and manipulate the Office Spreadsheet component with no host form or page. You'll see an example of this technique, later in the chapter.

Note:   The concept of using COM components with no visual interface isn't unusual. As a matter of fact, that's how all communication between Access and ADO works. It is somewhat unusual for an ActiveX control to support both types of activation (that is, on a form, and from code). This additional functionality gives you more flexibility when using these controls.

Office PivotTable

The Office PivotTable component allows you to analyze data, both from relational OLE DB data stores and from OLAP cubes. This powerful tool makes it simple for you to provide not only summary data (as you might in an Access crosstab query), but the control also allows users to interactively change the perspective on the data, viewing different field sets, sorting, grouping, and calculating values based on your data. Because the Office PivotTable control has a somewhat complex user interface, we've included a walkthrough, demonstrating how you might interact with the control, later in the chapter. Figure 12.2 shows the results of that walkthrough.

Figure 12.2. The Office PivotTable control makes it easy to create a summarized view of data that can be modified at runtime (click picture for larger image).

Office Chart

The Office Chart control allows you to create almost all the standard Excel chart types, plus a Polar chart. You can display multiple charts within the same charting space, allowing you to overlay different views of data on a single form or page. This control is far less resource-hungry than the standard Microsoft Chart component, and you can bind it to any of the other Office Web Components. That is, this control can retrieve its data from a spreadsheet, pivot table, or data source control. In addition, you can send it data using a standard ADO recordset or even by passing in arrays containing the data points to be displayed. For Office XP, Microsoft added one more data source type: You can enter the data into a worksheet contained within the Chart control itself. You'll find examples, later in the chapter, of binding an Office Chart control to different data sources.

Data Source Control

The Data Source component is the least appreciated of the Office Web Components, mostly because it's invisible. You'll never see this control—it has no user interface—but it does all the data binding work behind the scenes. Whenever you use a data access page within Access, the Data Source control is what provides the recordsets to be displayed on the page. This control acts as an intermediary between the database engine and the components that need the data, managing connections and providing recordsets as necessary. As you'll see, you can programmatically manipulate this control, adding to its collection of RecordsetDefs (objects that contain the information necessary to open a particular recordset) and working with its collections of groupings.

In this chapter, we won't focus on programming the Data Source control. You won't do it often, and when you do, you'll need more information than we have room for here. On the other hand, as an Access developer, you'll most likely find it easy to get started with the Data Source control, so we'll provide an example or two to get you going.

Who Can Use These, and Where?

Basically, the Office Web Components are ActiveX controls, just like most other ActiveX controls. If you embed them on an Access (or any other) form, they need to be installed on users' machines before your forms will display correctly. If you use them on web pages, they'll need to be available on users' machines before the pages will display correctly. That is, they're no different from any other ActiveX control in how you deploy them as part of an application.

One difference, however, between these controls and other ActiveX controls you might embed on a web page is that many Office XP users will already have these installed on their machines. What if they don't? The DLL required by the Office Web Components (owc10.dll) isn't small—it's over 7 megabytes—but that shouldn't affect any of your users. Office plays a neat trick here: when you embed one of these components onto a web page, the CodeBase property of the control (that is, the location where the browser will look for the control if it's not already installed) is set to be the location from which you installed Office. Therefore, web access to pages including these controls won't attempt to download the largish DLL from your site—instead, the browser will attempt to install them from the original installation location. You can override this behavior, but you needn't. The behavior of the page when Office XP has not been installed locally will be addressed later in this section.

Unlike most ActiveX controls, the Office Web Components don't allow unrestricted distribution. That is, when developers purchase most other ActiveX controls, they're purchasing an unlimited distribution license. You generally have the right to distribute applications written using those controls to anyone. The Office Web Components require each user to have an Office XP license for full functionality. Does that mean that every user must have installed Office XP? No, not at all. There are two scenarios where a user can work with pages containing Office Web Components without having Office XP installed. In the first scenario, the user has a license for Office XP, but the product is not installed. The components will work on that user's machine just as though Office XP were installed. In the second scenario, the user has neither the Office XP product nor a license for the product. That user can view the controls, but can't make changes. Specifically, the user will not be able to:

• View the Commands and Options dialog box, which provides an interface to the properties of an Office Web Component
• Change the data or structure of a control
• Use any buttons on the control other than the Help and About buttons

The user can, however, view and print data in the control, expand and collapse PivotTable items, refresh a PivotTable, and select different sheets in a Spreadsheet control.

When a page loads, code in the page first checks to see whether the Office Web Components are installed. If they are not, the user sees a message with a link to install the necessary files (from the Office XP installation CD, by default) and a link to a Microsoft web page that offers more information. The components can be installed from the Office XP installation CD, downloaded from any of several Microsoft web pages, or downloaded from a central location you specify on your intranet.

So how does the component determine whether a given user has a license when Office XP is not installed? For example, a company might purchase several hundred licenses and roll out installations as necessary. Each user at the company has a valid Office XP license, but doesn't necessarily have Office XP installed. When such a user opens a page containing an Office Web Component, the component first checks to see if Office XP is installed. If not, the component checks for an Office XP license. A developer in such an environment can create a license file using the License Package Toolkit (available in Visual Studio or as a download from Microsoft's website). The developer can then add an <OBJECT> tag describing the location of the license file to the HTML file for each page that uses an Office Web Component.

Note:   Obviously, we're just touching on the issues you'll need to consider when deploying pages containing Office Web Components. For more information, you'll want to read Microsoft's own articles on the subject, beginning with knowledge base article number Q288732, OFFXP: How to Deploy the Office Web Components.

What about Netscape users? We haven't experimented with Netscape and ActiveX controls, and so we can't vouch for the plug-in that allows you to run ActiveX controls in that browser. One issue to consider is data binding. That is, once embedded within the plug-in that allows the page to download and display an ActiveX control, will the controls be able to communicate correctly, and will they provide data sources from one to the other? We don't know, and Microsoft is (unsurprisingly) quiet on the issue. We suggest limiting your use of Office Web Components (and any ActiveX control, for that matter) to situations in which you can control the browser that's used. What that boils down to is: just as with data access pages, you're best off using the Office Web Components only in local or intranet applications. Public websites using Office Web Components will be a problem until Microsoft achieves its goal of world domination in the browser arena.

Tip:   When we created the demos for this chapter, we tried to create both an Access form and an Access data access page for most of the examples. It might appear that we were simply being generous, providing versions for two different sets of developers. Actually, it's just so much easier writing code in the VBA editor that we created forms first and then converted the code to VBScript for the corresponding data access page. You might find this a useful technique as well, although the conversion does require some extra time. What about browser support? Do these controls require Internet Explorer 5, as do data access pages? Although data access pages depend on functionality first available in IE5, the Office Web Components only require IE 4.01 or later. (IE 4 will fail to load the controls, as will IE 3.) If you intend to create standard HTML pages using the Office Web Components, the only requirement is that their browsers support ActiveX controls fully.

Server-Side Behavior

The Office Web Components all have an interesting, and potentially useful, ability. Each can be created programmatically, without a visible representation on any host form or page. That is, you can write code like this:

' In VBA code:
Dim owcSS as OWC10.Spreadsheet
Set owcSS = New OWC10.Spreadsheet

' or, in VBScript code:
Dim owcSS
Set owcSS = CreateObject("OWC10.Spreadsheet")

Then, you can work with owcSS in your code as if referring to an Excel spreadsheet. You can do the same for pivot tables and charts. They add an extra feature: in both cases, you can use the controls' ExportPicture method (for Charts or PivotTables) or GetPicture method (Charts only, but faster than ExportPicture, since it writes to memory rather than to a file) to save its image as a graphic image. This means that you can have an ASP page on a web server, providing static views of pivot tables and charts to web clients that don't support ActiveX controls or don't have the Office Web Components installed. You simply write code in the page to create the pivot table or chart, save the information as a graphic file, and then provide a page to the browser that includes the file you've created. (For more information on using this technique, see Chapter 14, Public Access Data on the Web.)

Working with Scripting

As with most ActiveX components, the Office Web Components provide a large number of enumerated data values. That is, the components provide named groups of numeric values that are appropriate as values for specific properties and parameters. If you've worked with scripting at all, you know that these constants aren't generally available when writing script because you haven't associated the components' type library with the host's environment at design time. Normally, you need to either declare the constants you need or include a file containing all of the constants.

To make this easier for you, the Office Web Components development team politely provided a property of each component—the Constants property—which is itself an object with a Property Get procedure for each constant value. For example, in VBA, you might write code like this to set the data source for an Office Chart:

owcChart.DataSourceType = chDataSourceTypeSpreadsheet

Because the Office Web Components type library includes a value for chDataSourceTypeSpreadsheet (the value is 1), the VBA code knows what to do. If you write the same code in VBScript, however, one of two things will happen:

• If you've added "Option Explicit" to your code, you'll receive a runtime error when the interpreter hits that line.
• If you haven't added "Option Explicit" to your code, VBScript will see the expression, not know its value, and create it with a value of 0.

Both alternatives are unacceptable, although the first is less unacceptable than the second. To make use of the constants, your best bet is to declare a variable to refer to the Constants object, and then set the variable the actual object property. You'll find code like this in many of our examples:

Dim c
Set c = owcChart.Constants
' And then, later in the code...
owcChart.DataSourceType = c.chDataSourceTypeSpreadsheet

Working with the Components at Design Time

If you're working within the data access page designer or the form designer in Access, a few properties of an Office Web Component are available in the standard property sheet available for all controls on a form or data access page. The Office Web Components also provide a Commands and Options dialog box, in which you can set properties appropriate to the current selection within the control. This Commands and Options dialog box (shown in Figure 12.3) allows you to select properties such as those of the Spreadsheet control (including formatting), areas to show and hide, and protection. Unless you've disabled it, the user can also see the Commands and Options dialog box and use it to make runtime changes to the control.

Figure 12.3. Use the Commands and Options dialog box to control the behavior of the Office Web Component at design time or runtime.

To test the Commands and Options dialog box, follow these steps:

1. Create a new data access page.
2. From the Toolbox, select the Office Spreadsheet control, and place an instance of the control on the Unbound section of the page.
3. Click on the control to select it.
4. Right-click on the control, and select Commands and Options from the context menu.

Once you've displayed the toolbox, try changing properties of the control. This window works much like the standard Access Property window, except that it works in environments that don't support their own Property window—a browser window, for example.

You may not want users to be able to display the Commands and Options dialog box at runtime. To disable this feature, make sure you set the AllowPropertyToolbox property of the control to False. (You can also set this property on the Commands and Options dialog box itself, on the Protection page.)

Note:   Throughout this chapter, you won't see much coverage of working with the user interface for the three controls that have a user interface. Our concern here is demonstrating how you can get started programming these controls. If you're interested in UI features, start with the online help. If you're interested in digging into the programming features in more depth, see Programming Microsoft Office 2000 Web Components by Dave Stearns, which is available from Microsoft Press (ISBN 0-7356-0794-X). Dave isn't updating the book for Office XP, but much of the original edition is still valid. He is maintaining an excellent website (at the time we went to press) at http://www.learnowc.com. It provides useful, up-to-date information about Office XP's rendition of the Office Web Components.

Using ActiveX controls on Access forms

In order to work with properties, methods, and events of an ActiveX control, you must always refer to the Object property of the control you see on the form. We've found it useful to create a private WithEvents variable in the form's module, and then, in the form's Load event procedure, set the variable to refer to the Object property of the ActiveX control, for example:

Private WithEvents owcSS As OWC10.Spreadsheet

Private Sub Form_Load()
    Set owcSS = Me.ActiveXCtl0.Object
End Sub

If you don't need to react to events of the object, leave out the WithEvents keyword. Either way, from then on, in your form's module, use your variable when you need to refer to the control. This way, you can use early binding when referring to the control, and you can use the same object variable when writing event procedures. You'll find this technique used in all the Access form examples in this chapter.

Working with VBA

When you use an external component from within VBA, you normally use the Tools . . . References menu item to set a reference to the associated type library. That way, you get the benefits of early binding, IntelliSense, the Object Browser, and local help for the external objects. Search as you might from within Access, however, and you won't find the reference you need in the References dialog box. (The reference you need is named Microsoft Office XP Web Components.)

Although it's not clear to us why, if you want to use the Office Web Components from within VBA, you'll need to set the reference manually. And you'll need to do it for each individual project.

Tip:   If you insert one of the Office Web Component controls onto a form, you won't need to follow these steps. Adding one of these controls to a form loads the type library for you. The only time you'll need to follow these steps is when you're using the controls without placing one on a form. (See the "Server-Side Behavior" section earlier in this chapter for more information.)

To work with the Office Web Components from VBA, follow these steps:

1. Choose Tools . . . References to bring up the References dialog box.
2. Click the Browse button, and search for the Web Components folder. By default, it's C:\Program Files\Common Files\Microsoft Shared\Web Components\10.
3. Find and select the file named owc10.dll—this file contains the type library you need.
4. Back in the References dialog box, look for the Microsoft Office XP Web Components selection, as shown in Figure 12.4. If you don't see this item, you've selected the incorrect DLL.

Figure 12.4. After searching for owc10.dll, you should see the associated item in the References dialog box.

Now that you've got a basic idea of what the Office Web Components provide you, and when and how you can use these components, it's time to dig into the individual components. The rest of the chapter investigates each of the components in turn, showing how you can create web pages using the controls individually and collectively.

Tip:   If you're going to be programming these components, you'll need to make sure you've installed the help files for the Office Web Components. The most important help file is owcvba10.chm. Make sure this file is installed if you want help on the object models, properties, methods, and events of the Office Web Components.

The Office Spreadsheet Component

If your form or page requires grid-based data entry or display, or it requires the ability to modify values in a grid and see immediate recalculations elsewhere, the Office Spreadsheet component is made for you. As you'll see, not only can the control display data you've entered, you can also bind individual cells to formulas or other controls on a page. The Office Spreadsheet control also provides an "invisible" server-side mode, where you can instantiate the control with no host of any sort, making it perfect for server-side calculations using the Excel spreadsheet calculation engine.

Tip:   It's hard to count the number of times we've been asked for a way to provide a checkbook-like data entry system written in Access. This is a perfect situation for the Office Spreadsheet control. Although you can "fake it" with a datasheet, Access simply doesn't natively support the concept of a vertically calculated value within a datasheet.

Using the Office Spreadsheet Control

Before you can use the Office Spreadsheet control, you normally place it onto a form or a web page. Getting an instance of the control onto a form is easy—you do it just like any other ActiveX control. That is, from your Access form, select the Insert . . . ActiveX Control . . . menu item (see Figure 12.5), and choose Microsoft Office Spreadsheet 10.0 from the list of available controls. Access will place an instance of the spreadsheet control on your form, filling the entire detail section.

Figure 12.5. Use the Insert . . . ActiveX Control menu item to add the Office Spreadsheet control.

If you're working in Access and want to take an existing Excel spreadsheet and display it on a data access page using the Office Spreadsheet component, you have a number of choices:

• If you're creating a data access page, you can select the region you'd like to have appear on your page from within Excel, and copy it to the Windows Clipboard. Then, with the Unbound section selected in the data access page designer, paste the selection. Access will create a new Office Spreadsheet component for you, and it will paste the region you've selected into the control. This isn't a perfect solution, however, as it loses some properties of the selected range, including protection. You'll need to manually enable protection for the new control, using its Commands and Options dialog box.

Tip:   Do not attempt the previous technique if you're creating an Access form—cutting and pasting an Excel region in a form creates an unbound object frame on the form, which causes all of Excel to be loaded when you run your form. If you're working with Access forms, you can add a new Office Spreadsheet control and cut and paste the Excel region into the sheet in the control.

• From within Excel, select the range you'd like to display in the control, and then select File . . . Save as Web Page . . . Choose the Selection option (rather than saving the entire workbook), and make sure you check the Add Interactivity check box. (See Figure 12.6.) You can change the title if you like, and select the output filename. Click the Save button when you're ready. Back in Access, create a new data access page, and from the New Data Access Page dialog box, select Existing Web Page. Find the page you just created, and you'll see that you've got an Office Spreadsheet control on the page, ready to use.

Figure 12.6. Use the Save as Web Page . . .  menu item in Excel to create a web page containing an Office Spreadsheet (click picture for larger image).

Working with the SpreadSheet Control

Once you've placed an Office Spreadsheet control onto a data access page, you're ready to work with it as if you were working directly with an Excel spreadsheet. The sample data access page (SSControl, which is shown in Figure 12.7) allows you to enter page counts for book chapters. Given the fact that all book chapters have an even number of pages, it calculates the printed page count and displays the total number of pages in an Unbound HTML control on the page. Take a moment and try the page—note that you can't enter data into the spreadsheet except in the middle column, and that as you type, all the totals stay current.

Figure 12.7. You can treat an Office Spreadsheet as a little Excel spreadsheet, as in this demonstration page (click picture for larger image).

The following bullet points highlight issues we handled when designing this page:

• In this example, we've hidden all of the hints that this is a spreadsheet at all, including the toolbar, the title bar, and the grid lines, using the Commands and Options dialog box for the control.

• We only need the first sheet, so we used the Workbook page of the Commands and Options dialog box to delete Sheet2 and Sheet3.

• To set up the formulas in column C, we entered the following value in cell C2:

  =B2+MOD(B2,2)
  

  This expression takes the value in cell B2 and adds to it the remainder you get when you divide the value in B2 by 2. This calculation forces the number of pages to the next even number. Then, to copy it to the rest of column C, we copied cell C2 to the Clipboard, selected cells C3:C15, and then pasted the formula. The spreadsheet fixes the references so that each cell in column C refers to the corresponding cell in column B.

• To calculate the sum of all the pages, we entered the following formula into cell C17:

  =SUM(C2:C16)
  

• The spreadsheet contains data in three columns and 17 rows. You only see 16 rows of data, because we set the Viewable Range for the control (on the Sheet page of the Commands and Options dialog box) to be A1:C16. The 17th row contains the sum for the data in the other rows, but it's hidden, because the page displays the sum from cell C17 outside the spreadsheet.

• You can only type data into the middle column of the spreadsheet. To protect the other cells, you can display the Commands and Options dialog box, select the Protection page, and protect the active sheet. Then, you must select the range of cells where you do want to allow user input, and display the standard Property sheet. Change the Locked property of each range that should be editable to False. Figure 12.8 shows the Protection page of the Commands and Options dialog box. Obviously, if you enable protection, you'll most likely want to disable use of the Commands and Options dialog box at runtime.

  

  Figure 12.8. Use the Protection settings to control which cells the users can modify and control the Locked property.

• Although the final row of the spreadsheet is hidden, you can see the total page count in an Unbound HTML control on the page. Code in the spreadsheet's SheetCalculate event and in the window's onLoad event fills in the data. If you look at the script associated with the page, you'll find these procedures:

  Sub Recalc()
      lblTotal.innerText = "Total Pages: " & _
       owcSS.Range("C17").Value
  End Sub
  
  Sub window_onLoad
      Call Recalc
  End Sub
  
  Sub owcSS_SheetCalculate(Sh)
      Call Recalc
  End Sub
  

(For more information on the Sh object passed as a parameter to the Office Web Components events, see the "Working with Chart Events" section later in the chapter.)

Discovering properties, methods, and events

Because the scripting editor doesn't provide an easy way to investigate members of the objects you're working with, you may want to mix development in both VBA and the script editor. We've found it easiest to set a reference to owc10.dll from VBA, and then use the Object Browser and online help to figure out how all the objects interact, what their properties, methods, and events are, and prototype code in the VBA editor. Even when working with a data access page, you can begin your coding efforts in VBA, so you get the benefit of the tools VBA provides.

For example, if you want to refer to a control on a particular data access page, you can use an expression like this:

' In the Declarations area...
Private WithEvents owcSS As OWC10.Spreadsheet

' In some procedure...
Set owcSS = DataAccessPages("dapTest").document.all("owcSSControl")

In this example, owcSSControl is the spreadsheet control on the data access page. From then on, in your VBA code, you could program against your VBA variable, referring to the control on the data access page. Of course, once you've got everything figured out, you can cut and paste the code over the script editor, removing syntax that VBScript won't allow. Although this technique isn't for everyone and every situation, it certainly made investigating these controls simpler for us.

Of course, the Microsoft Script Editor includes its own object browser, so you can get some of the benefits of the VBA Object Browser from within that environment as well. See Chapter 11 for more information on using that tool.

Programming the Spreadsheet

The following example, intended to show off some of the features of the Spreadsheet control's object model, allows you to select the name of a table in the database, and then fill an Office Spreadsheet with all the data from the table. You might want to display data in this grid format, or even allow users to calculate simple sums or work with the data offline.

This example works through many of the issues you need to solve in order to get data into an Office Spreadsheet control. We've provided this example both as an Access form (frmSSData) and as a data access page (SSData). In the text, we'll discuss the data access page example, only because its code is a bit less familiar to most readers. You might want to stop and experiment with either or both of these examples before reading on. (Figure 12.9 shows the data access page version of the example.)

Figure 12.9. Once you've selected a table or query name, script in the page fills the spreadsheet with all the data (click picture for larger image).

If you were to solve this problem yourself, you'd need to answer several questions (many of these questions deal with ADO issues, not spreadsheet issues):

• How do you open a connection to the current database?
• How do you provide a combo box full of available tables and queries?
• Once you've selected a table or query, how do you retrieve all its data?
• Once you have all the data, how do you insert it into the spreadsheet control?
• How do you "freeze" the title row so it doesn't scroll?

The following sections provide solutions to each of these issues.

Getting data from a recordset into a spreadsheet

If your goal is to place data from an open recordset into a spreadsheet, your first thought might be to simply loop through all the columns, in all the rows, and place each value into the appropriate cell, programmatically. Unfortunately, this technique is painfully slow. Even for moderately small recordsets, you'll be amazed at how long this takes. Luckily, the Office Spreadsheet control (and Excel itself) provides a method of the Range object named CopyFromRecordset that copies an entire ADO recordset to a given range on a sheet. You need only supply the ADO recordset and the desired location on the sheet.

Getting started

To get started, the script portion of the data access page contains a few declarations:

Option Explicit
Dim cnn ' ADODB.Connection

Const adSchemaTables = 20

The constant provides a necessary ADO constant. (If only ADO provided a Constants object, as all the Office Web Components do, scripting ADO would be a lot easier!) The window_onload event procedure, shown in Listing 12.1, sets up the Connection variable for the rest of the code.

Listing 12.1.

Sub window_onload()
   ' Use the connection from the DataSourceControl.
   Set cnn = MSODSC.Connection
   ' Load up the list of table/query names,
   ' and set up the empty spreadsheet.
   Call LoadSourceNames
   Call ResetSpreadSheet(owcSS)
End Sub

Code in the window_onload event procedure opens a persistent connection to the current database. Note that this code takes advantage of the fact that the Data Source control on the page (named MSODSC, by default) knows how to connect to the host database and sets a connection variable to the Data Source control's connection. This way, there's only one place within the page containing information about the location of the database. Finally, this code loads the combo box with a list of all the tables and queries in the database and sets the spreadsheet up, ready to contain to data.

Filling the list of table and query names

Next, you'll need to fill the combo box with a list of table and query names. This problem really has two halves:

• How do you retrieve a list of table and query names?
• How do you fill a combo box with the names you find?

Both questions have been answered elsewhere in this book, in one volume or another, but it's worth reviewing the answers here. Listing 12.2 contains the entire LoadSourceNames procedure, which does all the work.

Listing 12.2.

Sub LoadSourceNames()
   Dim opt
   Dim rst
   Set rst = cnn.OpenSchema(adSchemaTables)
   rst.Filter = "TABLE_TYPE = 'TABLE' Or TABLE_TYPE='VIEW'"
   Do Until rst.EOF
      Set opt = document.createElement("OPTION")
      opt.Text = rst.Fields("TABLE_NAME").Value
      opt.Value = opt.Text
      cboSource.add opt
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End Sub

LoadSourceNames begins by calling the OpenSchema method of the Connection object. This method retrieves a recordset containing information about the connection's schema—the actual information it retrieves depends on the constant you send it. (See Chapter 6 in Access 2002 Desktop Developer's Handbook for more information on using the OpenSchema method.) In this case, because the code sends the adSchemaTables constant, the recordset the method returns contains information about the tables (and queries) within the database. When you request information about tables, the recordset contains (among others) TABLE_NAME and TABLE_TYPE fields. In this example, you only want to include tables for which the TABLE_TYPE property contains either "TABLE" or "QUERY." Therefore, setting the Filter property in Listing 12.2 handles rows with those types, and disregards the rest. (The only reason we included this check here was to avoid displaying the system tables in the list.) The rest of the code takes care of retrieving the actual table name, creating a new Option element, and adding it to the combo box:

Set opt = document.createElement("OPTION")
opt.Text = rst.Fields("TABLE_NAME").Value
opt.Value = opt.Text
cboSource.add opt

In addition, this code fragment sets the Value property of each option, so that when you select a value from the list, the Value property of the combo box will contain the selected value. Finally, the code in Listing 12.2 closes the schema recordset.

Selecting a value

Once you've made a choice from the combo box, the control's Change event fires and runs the code shown in Listing 12.3. This code checks to make sure you've selected a value containing the name of a table or query from the combo box (the first item in the list is empty, allowing you to clear the spreadsheet). If not, the code calls the ResetSpreadsheet procedure, clearing the spreadsheet and resetting the caption. If you selected a valid name, the code opens a recordset based on the selected table/query and then calls FillSpreadsheet to display the data.

Listing 12.3.

Sub cboSource_onChange()
    Dim rst

    If Len(cboSource.value) > 0 Then
        Set rst = CreateObject("ADODB.Recordset")
        rst.Open cboSource.Value, cnn
        Call FillSpreadsheet(rst, owcSS, _
         "Data from " & cboSource.Value)
        rst.Close
        Set rst = Nothing
    Else
        Call ResetSpreadsheet(owcSS)
    End If
End Sub

Displaying the data

The FillSpreadsheet procedure, shown in Listing 12.4, sets up the spreadsheet, retrieves the data and inserts it into the spreadsheet, and then formats the spreadsheet correctly. The following paragraphs outline how this procedure does its work.

Listing 12.4.

Sub FillSpreadsheet(rst, owcSS, strCaption)
   With owcSS
      .ScreenUpdating = False
      .TitleBar.Caption = strCaption
   End With
   With owcSS.ActiveSheet
      .Protection.Enabled = False
      .UsedRange.Clear
      .Cells.Font.Name = "Tahoma"
      .Cells.CopyFromRecordset rst
      .UsedRange.Locked = False
      .Rows(1).Locked = True
      .Range("A2").Activate
      owcSS.ActiveWindow.FreezePanes = True
      .Protection.Enabled = True
   End With
   owcSS.ScreenUpdating = True
End Sub

Handling screen updates

In order to make the screen display as clean as possible, the procedure turns off screen updating before doing any work and then resets it when it's done, using the ScreenUpdating property of the Office Spreadsheet control:

With owcSS
    .ScreenUpdating = False
End With

' Do the work, and then...
owcSS.ScreenUpdating = True

Working with the TitleBar

The Office Spreadsheet control has, as one of its properties, a TitleBar object. You can set the Visible property to control whether you see the title bar. You can format the interior of the title bar, using the Interior property, which is itself an object. Its only property at this time is Color, and you'll end up with expressions like this:

owcSS.TitleBar.Interior.Color = "red"

Tip:   When specifying colors using the Office Web Components, you can use any of the normal HTML color values. That is, you can use named colors (like "red"), an RGB value (using the rgb function), or a hex value. See Chapter 10 for more information on using colors with data access pages.

In this example, the code sets the Caption property of the TitleBar object, so that it displays the strCaption parameter passed to the procedure:

With owcSS
    .TitleBar.Caption = strCaption
End With

Setting up the active sheet

Next, the procedure retrieves a reference to the ActiveSheet object. It then uses three properties of the Worksheet object—Protection, UsedRange (the entire range that's been used), and Cells (the entire set of cells in the sheet)—to disable protection, to clear out the used range of cells and to set the font for the entire sheet:

   With owcSS.ActiveSheet
      .Protection.Enabled = False
      .UsedRange.Clear
      .Cells.Font.Name = "Tahoma"
   End With

Retrieving the data

If there are any rows to retrieve (that is, if the recordset properties BOF and EOF aren't both True), it's time to transfer the rows to the worksheet. In the Office 2000 version of the Web Components, this required several steps. In Office XP, however, the work is reduced to a single line of code:

With owcSS.ActiveSheet
   .Cells.CopyFromRecordset rst
End With

Formatting the sheet

The final step of this example formats the sheet correctly. Specifically, it seemed reasonable to "autofit" the used columns, make the field headers bold and locked (with the rest of the sheet unlocked), and to "freeze" the top row, so that as you scroll the data, the field names stay fixed.

To accomplish these goals, the FillSpreadsheet procedure in Listing 12.4 includes the following code:

With owcSS.ActiveSheet
   .UsedRange.Locked = False
   .Rows(1).Locked = True
   .Range("A2").Activate
   owcSS.ActiveWindow.FreezePanes = True
   .Protection.Enabled = True
End With

Note that in order to lock the top row, but none of the rest, the code must first unlock all the data, then lock the first row, and finally set the Enabled property of the entire sheet's Protection object to True.

To freeze a section of the sheet, you call the FreezePanes property of a Window object. Setting FreezePanes to True freezes the cells above and to the left of the active cell, so you must first move the cursor to cell A2 to freeze all of the first row.

Using the Spreadsheet Component without a Host

As part of your application, you may find that you need to take advantage of the calculation engine of an Excel-like spreadsheet. Or, perhaps you need to use one of the many built-in spreadsheet functions that aren't available in VBA. In either case, you don't need to display the user-interface for the spreadsheet. With most ActiveX controls, you would need to place a hidden instance of the control onto a form somewhere, and then use the features of the control programmatically.

You can accomplish these goals because the Office Spreadsheet component does not need to be hosted on a form, or on a page, in order to be instantiated and do its work. You can interact with the control, totally invisibly, with no user interface at all. This not only makes it possible to perform spreadsheet-like calculations from an ASP page on a web server, without end users having a copy of the Office Web Components locally, but it also reduces the overhead required in using the control locally.

Imagine this scenario: you want to calculate the median number of items sold in an order, for all orders where more than one item is sold. Access queries can calculate the sum, count, max, min, and other aggregate values, but they cannot calculate the median of a list of numbers. Sure, you could write your own Median function (and it's not even very difficult); however, if you need more than just this one simple calculation (use your imagination here), it makes sense to have the Office Spreadsheet do the work for you. This example, shown in Listing 12.5, opens a recordset, retrieves its data into a string (much like the previous example, but in a simpler case), creates an Office Spreadsheet component invisibly, inserts the data, sets up a cell to perform the calculation, and retrieves the data from the spreadsheet. You can find the code shown in Listing 12.5 in the module basSSInMemory.

Listing 12.5.

Function TestOWCSS() As Long
    ' Return the median number of items
    ' sold on a single order, when the number
    ' of items sold is greater than 1.
    Dim ss As OWC10.Spreadsheet
    Dim rst As ADODB.Recordset
    Dim lngRows As Long

    ' Normally, you'd add error handling to a procedure
    ' like this, but this is just attempting to prove
    ' a point.

    Set rst = New ADODB.Recordset
    rst.Open _
     "SELECT Quantity FROM tblOrderDetails " & _
     "WHERE Quantity > 1", _
     CurrentProject.Connection, adOpenForwardOnly, _
     adLockReadOnly, adCmdText


    ' Create the OWC Spreadsheet, in memory. Insert
    ' the values, using the recordset. Calculate
    ' the number of rows in the recordset (you couldn't
    ' get this from ADO because you opened a forward-
    ' only recordset).
    Set ss = New OWC10.Spreadsheet
    ss.ActiveSheet.Cells.CopyFromRecordset rst

    rst.Close
    Set rst = Nothing

    lngRows = ss.ActiveSheet.UsedRange.Rows.Count

    ' Set up the formula, in cell B1, and return the value.
    With ss.Range("B1")
        .Formula = "=MEDIAN(A2:A" & lngRows & ")"
        TestOWCSS = .Value
    End With

    ' Release the spreadsheet component.
    Set ss = Nothing
End Function

In this example, the code starts by doing all the necessary work with the recordset. That is, it opens the appropriate recordset, uses CopyFromRecordset to copy the data to the worksheet, and then closes the recordset.

    Set rst = New ADODB.Recordset
    rst.Open _
     "SELECT Quantity FROM tblOrderDetails " & _
     "WHERE Quantity > 1", _
     CurrentProject.Connection, adOpenForwardOnly, _
     adLockReadOnly, adCmdText

    Set ss = New OWC10.Spreadsheet
    ss.ActiveSheet.Cells.CopyFromRecordset rst

    rst.Close
    Set rst = Nothing

The code next calculates the number of rows in the data, using the UsedRange property of the Spreadsheet component. The code uses this value later, when it calculates the Median of the correct range:

    lngRows = ss.ActiveSheet.UsedRange.Rows.Count

Finally, the code inserts a formula into cell B1 on the spreadsheet, requesting the median of the inserted data, and then releases the reference to the Spreadsheet component:

    With ss.Range("B1")
        .Formula = "=MEDIAN(A2:A" & lngRows & ")"
        TestOWCSS = .Value
    End With

Basically, anything you can do with the Office Spreadsheet component visibly, you can do with it invisibly as well. You can, if you like, think of the Office Spreadsheet component as a calculation engine, rather than as a grid. Certainly, you'll need to investigate how to interact with spreadsheets in general—a much different task than working with databases—before making the best use of this tool, but the Office Spreadsheet component makes a useful grid for data entry, calculation, and display.

The Office PivotTable Component

In Excel, a pivot table is a special, interactive table that summarizes large amounts of data. You can easily rotate the rows and columns to get a different perspective on your data, and you can filter the data by displaying different pages. After you create the basic pivot table, it can easily be "pivoted." Pivoting involves dragging and dropping fields to new positions within the table for a different view of the data. Because the pivot table acts as a view on the data, you can't change or alter the original data source by pivoting. This allows you to adjust the pivot table in a myriad of ways without concern for harming the original data source.

If you want to, you can easily create a pivot table in Excel and save it as a web page, with interactivity. (See the "Using the Office Spreadsheet Control" section earlier in the chapter to review how you can publish an existing Excel solution to the Web, using the Office Web Components.) The real question for Access developers, however, is why you might want to use a pivot table at all.

To clearly describe the usefulness of a pivot table, take a look at a real-world example. You work for a pizza store, and your manager has asked you to create a summary report for some historical data on product sales. She wants to know the answers to questions like these:

• What were the annual sales totals for each customer, category of products, and product?
• How many units were sold for each product?
• Which customers had the highest orders across the span of three years?
• Are certain categories selling more than other?

You're good with Access, and so you can easily create a query that combines all the necessary tables together to get the raw data. (In the sample database, that query is qryOrderDetails, shown in Figure 12.10.) The list consists of rows of sales information for each product, including the product category, the employee who sold it, and the date on which it was sold. However, it will take some serious work to combine the information to answer the questions your manager is asking. At this point, you have two options: create several reports, each providing the answer to a specific question; or find a tool that allows you to quickly summarize the information interactively—the pivot table.

The most important feature of a pivot table is its ability to summarize large amounts of data into a clear and flexible format. By following the steps described in the next section, you can quickly create a summary report for the sales data like the one shown in Figure 12.11. You can easily adjust the new pivot table for various views and calculations based on the data. For instance, your manager can now easily:

• Filter the data displayed by the pivot table to see only sales information for pizza products.
• Change the sales data to reflect averages instead of total sales.
• Add the Month or Year field to see data grouped by month or year.
• Hide specific products that don't need to be seen.

Figure 12.10. The qryOrderDetails query (click picture for larger image)

Your manager should be more than satisfied with the flexibility of the pivot table that you have created as well as with your resourcefulness. And there you have it: wealth and fame based on the capabilities of the Office PivotTable component!

Why use a pivot table? Pivot tables can be used in almost any environment to simplify data analysis. Rather than creating multiple static reports based on the same data source, you can opt to create a single pivot table that can be altered by the user to fit their specific needs. Some additional benefits to including pivot table reports in your analytical arsenal include the following:

Data comparison

Pivot tables allow you to easily compare field values across multiple categories. By limiting the amount of detail displayed, you can quickly compare subtotals for various groups.

Summarizing large groups of data

A large list of data is easier to understand when placed in a pivot table. Rather than hunting for the rows of information you need, a pivot table can quickly and easily group similar data together for analysis.

Tip:   This whole section will make much more sense once you've done a little work with pivot tables. On the other hand, most of our examples look and feel like simple Access crosstabs, so if you have ever created a crosstab query, you should be all set for now. To fully take advantage of the Office PivotTable control, using either its user interface or its object model, you'll need to understand more about pivot tables and their underlying technology than we can discuss here.

Note:   This isn't the place to delve deeply into how pivot tables work in Excel, nor how to create them. If you're interested in digging deeper into this technology, you might start with Mastering Microsoft Excel 2002 by Gini Courter and Annette Marquis (Sybex, 2001).

Getting Started with the Office PivotTable Control

Although we tend to avoid step-by-step walkthroughs in this book, using pivot tables is so foreign to most Access developers that working this exercise together seemed worthwhile. To get you comfortable with the Office PivotTable control, this section walks you through creating a data access page containing an instance of the control. By the end of these steps, you'll have created the pivot table shown in Figure 12.11. Your goal is to group the data in qryOrderDetails first by Category, and then by MenuDescription, calculate the amount sold of each item, grouped by months, and filter based on the customer.

Figure 12.11. After working through the steps in this section, you'll have a pivot table that looks something like this (click picture for larger image).

Tip:   If you haven't worked through Chapter 10, this section may be difficult going. We suggest you take time now to read and try out the examples in that chapter before continuing with this chapter.

To build your own data access page, including an Office PivotTable control, follow these steps:

1. In the sample database for this chapter, create a new data access page in Design view.

2. From the Field List window, find the qryOrderDetails query, and then click and drag it onto the Unbound section of the new data access page. Select PivotTable from the Layout Wizard and click the OK button. This creates an Office PivotTable control for you. (This action does not place a NavigationBar control on the form, but it does bind the Office PivotTable to the invisible Data Source control (named MSODSC) on the page.)

3. To make things easier, expand the new PivotTable control to fill as much space as possible on the data access page. It doesn't need to fill your whole screen, but you should make it at least four times as large as the default size for the control.

4. Right-click the PivotTable control to bring up the context menu, and select Commands and Options.

5. In the Commands and Options window, select the Behavior tab and make sure that all items in the Show/Hide section are selected.

6. Click on the title bar for the control. Select the Captions tab of the Commands and Options dialog box and try changing the caption to see how the dialog box works.

7. You won't need the OrderDate field, so remove it. To do that, click and select the field title, then drag the field somewhere on the page outside the control. (The cursor will change to indicate that you're removing a field.) Release the mouse. (There are other ways to remove a field, just as there are multiple ways to accomplish just about anything with this control. This is the method that's simplest to demonstrate, however.) Repeat for the City field, which you also don't need in this example.

8. It's time to start grouping, so drag the MenuDescription field to the Drop Row Fields Here section, creating a row header. Notice that you've now grouped the data by MenuDescription, as shown in Figure 12.12.

   Tip:   As you drag the field, watch the blue border markers. These indicate where you'll be dropping the field.

9. Drag the Category field to the left of the MenuDescription field, creating a second row header.

10. Drag the FullName field to the Drop Filter Fields Here section. This creates a filter field, allowing you to control exactly which rows of data appear in the pivot table.

11. Select the Quantity column, right-click, and then choose AutoCalc . . . Sum. (You can get the same effect by clicking the Autocalc button on the toolbar.)

12. Stop here and try the Expand context menu item (or the Expand toolbar item) for the Category and MenuDescription fields. By expanding and contracting these fields, you can see how much of any item has been sold, both by category and by item. Also, try clicking the dropdown arrow next to the FullName field. Select a single customer to filter the rows for just that particular customer. At this point, your page should look something like Figure 12.13.

Figure 12.12. Creating a new row header groups the data by that field (click picture for larger image).

Figure 12.13. After setting up row headers and filter fields, your page should look like this (click picture for larger image).

Where is the data coming from?

Data for the PivotTable component can come from a number of different places:

Relational data

Using OLE DB: In this case, you're counting on the control to create its own summary data, and to group and filter fields as you request. That is, you request two-dimensional sets of data from some database engine, and the control does the work of creating third (and more) dimensions, based on summaries and groupings.

OLAP data

Normally from some OLAP server, such as SQL Server 2000: In this case, all the work is done remotely, so the performance is significantly improved. In addition, OLAP servers generally allow you to create multidimensional representations of your data (normally called cubes) that can be stored. Therefore, when you need to retrieve data from the cube, the server has already performed the necessary calculations and can quickly retrieve the information you request via the Office PivotTable component. You don't even need to be attached to a server. That is, you can save a cube file to disk and retrieve data from that. Of course, in that case, the performance will be a little slower, as the control must load much of the cube file into memory on first use. When you use a server-side cube file, the dimension structures are most likely already in RAM and can load faster.

XML data

Created using the Save method of an ADO recordset: ADO allows you to save an opened recordset in XML format. Using this data with an Office PivotTable requires a separate bit of OLE DB "plumbing," and won't be discussed here. (If you're interested in using XML data with the Office PivotTable component, you'll need to investigate the mspersist OLE DB provider. For more information, see Programming Microsoft Office 2000 Web Components by Dave Stearns, which is available from Microsoft Press.)

Tip:   If you want to expand or contract and entire column, select the header for the column first, then use the Expand menu or toolbar item.

1. To group the sales information by month, you need to add a column header field. If the PivotTable Field List window isn't visible, right-click the PivotTable and choose Field List from the context menu.
2. From the PivotTable Field List window, find the OrderDate by Months item and open the node (click on the "+") to select Months. To create a column header field, drag this to the Drop Column Fields Here section. Now you see all the sums broken out by months. To see information for a particular year, select the Months dropdown list, and choose one or more years.
3. The caption for that dropdown list might be confusing to your end users, so use the Commands and Options window to change it. In the Office PivotTable control, select the Months dropdown, and then in the Commands and Options window, click the Captions tab. Change the caption from Months to Select Years.
4. Switch to Page view, and try changing all the options. You can even move fields around at runtime. For example, you can move the Months to be a filter field, and then choose years that filter the data.

These steps walked you through the bare minimum use of the Office PivotTable control. You've seen how to create the control and bind it to data, how to move columns around, and how to filter data. As you can see, you can make changes to the design of the control both at design time and at runtime.

Programming the PivotTable Control

Before you can work with the PivotTable control programmatically, you'll need to know a few things about its object model. First of all, the control itself has two completely distinct object hierarchies:

PivotView object

Allows you to manipulate the view presented in the control. Using this object, you can add fieldsets (that is, one or more logically grouped sets of fields) to the various axes (row, column, data, or filter). You can also use this object to add totals and modify the way any field or cell is displayed. Use the ActiveView property of the Office PivotTable control to retrieve a reference to this object.

PivotData object

Allows you to manipulate the data filling the PivotTable control. This object actually represents the data displayed in the control, and it allows you to get at specific data items and at the available fields. Use the ActiveData property of the Office PivotTable control to retrieve a reference to this object.

Note:   Although it's not part of the PivotTable control explicitly, you can't forget about the Data Source control that's providing data for all the examples you'll see here. You can bind the Pivot- Table control to various data sources, but all the examples here use the Data Source control.

In this section, we'll work through two examples using the PivotTable control. In the first example, you'll see how to programmatically "pivot" the control, how to change the layout of the control, and how to react to events of the control. In the second example, you'll see how to persist the layout information, so that you can save and restore layout information from one session to another.

Working with the PivotView Object

This example is based on the sample data access page, PivotTableObjects, shown in Figure 12.14. This page includes a pivot table, similar to the one you created in the "Getting Started with the Office PivotTable Control" section, as well as a series of check boxes that allow you to control the behavior of the Office PivotTable control. The following sections explain what happens when you select each check box, how the check boxes are initialized when the page first opens, and how the page keeps the check boxes current, even if you make changes to the layout using the control's user interface directly.

Note:   In the examples that follow, all code fragments will include the With...End With constructs if necessary, so you can tell which objects are being manipulated. The actual code may look slightly different, but we've attempted to make each fragment readable on its own.

Figure 12.14. Work with this example to see how you can manipulate and react to changes to the PivotTable control's interface (click picture for larger image).

Making things happen

As you can see in Figure 12.14, the page contains three check boxes that control the "look" of the control (toolbar, title bar, and drop areas); two check boxes allowing you to group on City and Category; and two check boxes allowing you to filter and total. Most of these check boxes take an action that affects the ActiveView property of the PivotTable control, and the following paragraphs describe the actions of each one.

Note:   Throughout this example, code will refer directly to the PivotTable control on the sample page, which is named pt.

Changing the control layout

The Show Toolbar? check box controls the display of the PivotTable control's toolbar. In the onClick event for the check box, the associated code sets the DisplayToolbar property of the PivotTable control to be the same as the checked property of the check box:

Sub chkShowToolbar_onClick()
    pt.DisplayToolbar = chkShowToolbar.checked
End Sub

Note:   DisplayToolbar is a property of the control itself, rather than of the ActiveView property of the control. This is the only property of the control itself that this example modifies.

The Show Titlebar? check box sets the Visible property of the ActiveView's TitleBar property to be the same as the checked property of the check box. (The TitleBar property is itself a PivotLabel object, with other properties such as Caption, Font, BackColor, ForeColor, and HAlignment.) When you click this check box, the following code executes:

Sub chkShowTitlebar_onClick()
    pt.ActiveView.TitleBar.Visible = _
     chkShowTitleBar.checked
End Sub

The Show Drop Areas? check box controls the display of three different "areas" of the control: the filter area, the column header area, and the row header area. Each of these areas corresponds to a different object property of the ActiveView object: the RowAxis, ColumnAxis, and FilterAxis properties. In each case, the code needs to set the Label property's Visible property to match the state of the check box:

Sub chkShowDropAreas_onClick()
    Dim blnShow
    blnShow = chkShowDropAreas.checked

    With pt.ActiveView
        .RowAxis.Label.Visible = blnShow
        .ColumnAxis.Label.Visible = blnShow
        .FilterAxis.Label.Visible = blnShow
    End With
End Sub

Changing the row headings

The next two check boxes (Group by City? and Group by Category?) work with row headers, adding or deleting the City or Category fields from the row header area. In each case, the code adds or deletes a FieldSet object from the RowAxis object's FieldSets collection. What's a FieldSet? Although each field acts individually in this simple example, in some pivot table scenarios you might have groups of related fields, such as OrderDate and DeliverDate for a particular order. Each FieldSet object in the pivot table can be added or removed from the axes' collection of fieldsets. In this case, because all the FieldSet objects contain only a single field, it's easy to refer to them by name—the unique identifier for each item in the collection is the name of the field itself.

Listing 12.6 shows the code that's run when you click the Group by City? check box (the code for the Group by Category? check box is the same, except for the field name). If you select the check box, then code runs that adds a FieldSet to the RowAxis object's FieldSets collection, like this (effectively):

With pt.ActiveView
    If chkGroupByCity.checked Then
        .RowAxis.InsertFieldSet .FieldSets("City")
    End If
End With

The full syntax for the InsertFieldSet method looks like this:

object.InsertFieldSet(FieldSet, [Before], [Remove])

where:

• object is an object that can insert a FieldSet, such as a RowAxis, DataAxis, FilterAxis, or ColumnAxis object.
• FieldSet specifies the field set to be inserted.
• Before specifies the index of the field set before which the inserted field set will be placed. It can be a numeric index or a unique string value.
• Remove is reserved for future use. When you add a field set to an axis, it's always removed from any other axis.

The InsertFieldSet method guarantees that when it adds the FieldSet to a new axis, it removes it from its old location. In other words, this code removes the City FieldSet from the DataAxis collection of FieldSets and adds it to the RowAxis FieldSets collection.

If you uncheck the check box, the code has to do a little more work. First of all, it must expand the column before removing it from the RowAxis FieldSets collection—it appears that, depending on the order in which you add and remove items, the data may simply disappear if you don't expand the column before removing it. (We learned this after noticing all the columns go away in the pivot table while working on this example. There may be other solutions, but this one works.) That code looks like this:

With pt.ActiveView
    .RowAxis.FieldSets("City"). _
     Fields(0).Expanded = True
End With

Note:   You expand a full column by setting the Expanded property of the Field object, not the FieldSet object. (Remember, a FieldSet might contain multiple fields—these don't—and because there's only one field, you know it's field number 0.)

Next, the code removes the FieldSet from the RowAxis' collection of FieldSets:

With pt.ActiveView
    .RowAxis.RemoveFieldSet "City"
End With

The syntax for the RemoveFieldSet method looks like this:

object.RemoveFieldSet(FieldSetKey)

where:

• object is an object that has a FieldSets collection, such as a RowAxis, DataAxis, FilterAxis, or ColumnAxis object.
• FieldSetKey specifies the field set to be removed. Specify a numeric index or a unique string key value.

In this case, however, simply removing it from the RowAxis FieldSets collections isn't enough. You must also add it back to the DataAxis FieldSets collection, like this:

With pt.ActiveView
    .DataAxis.InsertFieldSet .FieldSets("City")
End With

Tip:   When you insert into a FieldSets collection, you must supply an entire FieldSet object. When you remove from the collection, you can simply supply an index or a unique key value. Although you can pass a FieldSet object, you don't have to do so.

Listing 12.6.

Sub chkGroupByCity_onclick()
    With pt.ActiveView
        If chkGroupByCity.checked Then
            .RowAxis.InsertFieldSet .FieldSets("City")
        Else
            ' Make sure the field is expanded.
            ' Otherwise, it may get lost when you
            ' add it back to the data axis.
            .RowAxis.FieldSets("City"). _
             Fields(0).Expanded = True
            .RowAxis.RemoveFieldSet "City"
            .DataAxis.InsertFieldSet .FieldSets("City")
        End If
    End With
End Sub

Adding a filter

To add or remove a filter field, you'll need to work with the FieldSets collection of the FilterAxis object. The code required to do this is not much different than the code required to create a row header:

Sub chkFilterOnFullName_onclick()
    With pt.ActiveView
        If chkFilterOnFullName.checked Then
            .FilterAxis.InsertFieldSet _
             .FieldSets("FullName")
        Else
            .FilterAxis.RemoveFieldSet "FullName"
            .DataAxis.InsertFieldSet .FieldSets("FullName")
        End If
    End With
End Sub

This code is slightly simpler, however, because there's no need to expand the column before removing it. Other than that, the only difference between adding a row header and adding a filter is that for row headers, you work with the RowAxis object, and for filters, you work with the FilterAxis object.

Adding a total

To add a new total, you must take two steps. You must first add a new PivotTotal object to the PivotView's collection of totals, and then you must display the total on the axis where you want to see it. To create the PivotTotal object, you call the AddTotal method of the PivotView object. The syntax for this method looks like this:

object.AddTotal(Name, Field, Function)

where:

• object is the PivotTable control's PivotView object.
• Name specifies the name of the total. You needn't supply a name, and you can use an empty string instead.
• Field specifies the field to be used to create the total. This must be a PivotField object, normally a field within a FieldSet.
• Function specifies the function to be used to create the total. It can be any of plFunction- Average (5), plFunctionCalculated (127), plFunctionCount (2), plFunctionMax (4), plFunctionMin (3), , plFunctionStDev (6), plFunctionStDevP (10), plFunctionSum (1), plFunctionUnknown (0), plFunctionVar (7), or plFunctionVarP (11). (All these values are provided as properties of the Constants object of the PivotTable control.)

To insert the total into an axis, you call the InsertTotal method (which adds the PivotTotal object to the PivotTotals collection of the axis), with syntax like this:

object.InsertTotal(Total, [Before])

where:

• object is a PivotDataAxis object (in this example, the DataAxis property of the PivotView).
• Total specifies the PivotTotal object to be inserted.
• Before specifies the index of the total before which the inserted total will be placed. If you do not specify this argument, the total is inserted at the end of the collection.

Finally, to remove the total, you can either remove it from the view or from the axis. In this example, the code deletes from the view, using the DeleteTotal method. The syntax for the DeleteTotal method looks like this:

object.DeleteTotal(Total)

where:

• object is a PivotView object.
• Total specifies the name or number of the total to be removed.

Given those three methods, the Total on Quantity? check box executes the following code when you select it:

Sub chkTotalOnQuantity_onClick()
    With pt.ActiveView
        If chkTotalOnQuantity.checked Then
            ' Add a new total to the view. There's only
            ' one field in the Quantity fieldset, so use
            ' that for the total. Once you create the
            ' total, you need to show it on the "data"
            ' axis.
            .AddTotal "", _
             .FieldSets("Quantity").Fields(0), _
             c.plFunctionSum
            .DataAxis.InsertTotal .Totals(0)
        Else
            ' Simply delete the total from the view.
            ' That deletes it from the data axis, as well.
            .DeleteTotal .Totals(0)
        End if
    End With
End Sub

Handling options at startup

When you first open the page, the various check boxes should correctly reflect the current settings of the options with which they are associated. To make that happen, the page runs a series of procedures at startup, making sure that the correct check boxes are checked. The window_onload event procedure runs two other procedures in the page, HandleViewChecks and HandleDataChecks. Each of those procedures calls several other procedures, which check the status of various properties and set the associated check boxes' values. Listing 12.7 shows HandleDataChecks and the procedures it calls. Listing 12.8 shows HandleViewChecks and the procedures it calls.

Listing 12.7.

Sub HandleDataChecks()
    HandleGroupBy
    HandleTotal
    HandleFilter
End Sub

Sub HandleGroupBy()
    ' Check to see if the "Group by..." check boxes
    ' should be checked. In each case, look for the
    ' orientation of the fieldset object.
    With pt.ActiveView
        chkGroupByCity.checked = _
         (.FieldSets("City").Orientation = _
         c.plOrientationRowAxis)

        chkGroupByCategory.checked = _
         (.FieldSets("Category").Orientation = _
         c.plOrientationRowAxis)
    End With
End Sub

Sub HandleTotal()
    On Error Resume Next
    Dim strName
    With pt.ActiveView
        Err.Clear
        strName = .DataAxis.Totals("Sum of Quantity").Name
        chkTotalOnQuantity.checked = (Err.Number = 0)
    End With
    Err.Clear
End Sub

Sub HandleFilter()
    With pt.ActiveView
        chkFilterOnFullName.checked = _
         (.FieldSets("FullName").Orientation = _
          c.plOrientationFilterAxis)
    End With
End Sub

Listing 12.8.

Sub HandleViewChecks()
    HandleLabel
    HandleTitleBar
End Sub

Sub HandleLabel()
    chkShowTitleBar.checked = _
     pt.ActiveView.TitleBar.Visible
    chkShowDropAreas.checked = _
     pt.ActiveView.RowAxis.Label.Visible
End Sub

Sub HandleTitleBar()
    chkShowToolbar.checked = pt.DisplayToolbar
End Sub

In most of the procedures called by HandleDataChecks, the code works basically the same: it looks at the Orientation property of the appropriate FieldSet object. The procedure sets or clears check boxes based on the value of the Orientation property.

The code must handle totals differently, as there's no associated Orientation property. In this case, the code turns off error handling, clears the error number, attempts to retrieve the Name property of the object in question, and checks Err. Number afterward to tell if an error occurred. If so, the object doesn't exist, and the check box shouldn't be checked.

The procedures called by HandleViewChecks are simpler—they simply set the checked property of the appropriate check box to reflect the value of the associated PivotTable or PivotView property.

Reacting to events

Because the user of this page may change settings interactively, either by dragging fields around or by selecting options from the Property Toolbox, you want the check boxes on the page to stay "in synch" with the PivotTable control. In order for that to happen, you must find an event that occurs whenever the user changes an option, and write code to update the check box accordingly.

The PivotTable control raises several events as you work with it interactively, but the two required by this example are the ViewChange and Query events. The ViewChange event occurs whenever the user changes any aspect of the PivotView object. When the control raises the event, it passes an enumerated value to your event procedure, indicating one of 45 or so reasons why the event might have occurred. Given the reason the event occurred, your code can react to the event appropriately. These reasons include things like changing the state of the title bar or the drop areas, changing the width of a column, changing colors, expanding or contracting a field, and so on. In this example, the only two reason values you need to watch for are plViewReasonLabelVisibleChange and plViewReasonDisplayToolbarChange (both available to you as properties of the Constants property of the PivotTable control). Listing 12.9 contains the code for the ViewChange event.

Note:   Listings 12.9 and 12.10 include code to exit the procedure if the blnInitialized variable isn't True. This code ensures that these event procedures don't run when the page first loads. Because the control raises these events as it initializes, but before the window_onload event procedure runs, it doesn't make sense to run these unless the page has been completely loaded. Therefore, the blnInitialized variable is False until the end of the window_onload event procedure, which sets it to True.

Listing 12.9.

Sub pt_ViewChange(Reason)
    ' React to event that occurs when the
    ' pt's view changes.
    If Not blnInitialized Then
        Exit Sub
    End If
    Select Case Reason
        Case c.plViewReasonLabelVisibleChange
            Call HandleLabel
        Case c.plViewReasonDisplayToolbarChange
            Call HandleTitlebar
    End Select
End Sub

Note:   Hopefully, as you study the two event procedures in this example, you'll see why we broke up the various procedures that handle the check boxes on the page. Doing it the way we did makes it easier to call the same procedures at startup and later, when events occur.

The PivotTable control raises its Query event when you change some part of the query filling the control. The Query event doesn't offer any information about what triggered the event (although the Office 2000 version of the event did), so we're running all three of the check box update procedures here. Listing 12.10 shows the sample's Query event procedure.

Listing 12.10.

Sub pt_Query()
   ' React to event that occurs when the
   ' pt's query changes.
   If Not blnInitialized Then
      Exit Sub
   End If
   HandleGroupBy
   HandleFilter
   HandleTotal
End Sub

In this example, you've seen how to manipulate the PivotTable control's PivotView object. You've seen how to add and delete row headers, filters, and totals, as well as how to modify the various aspects of the control's interface. There are many more objects, methods and properties that we couldn't cover here, but hopefully, this example has given you enough to get started. You should be able to use the Object Browser to dig deeper, if necessary.

Note:   Why didn't we spend more time with the PivotData object? Unless you're interested in altering the workings of the PivotTable completely, or in creating a PivotTable control from scratch, you won't have much need to work with this object. The PivotView subtree of objects works with the view elements and the schema elements available as part of the view. The PivotData subtree (which we haven't discussed at all in this chapter) represents the results of the most recent query that provided data for the pivot table. You might use the PivotData subtree (starting with the ActiveData object) if you need to gain access to the query result data, using the Cells, Members, and Aggregates collections.

Persisting PivotTable Settings

If you allow a user to work with a PivotTable control (altering the groupings, filterings, totals, and so on), it might be nice to be able to save all the settings so that the user can retrieve them the next time the page is loaded. This turns out to be quite simple, as far as the control is concerned: the control politely provides the XMLData property that handles this issue. This read/write property allows you to both read (and store somewhere) and then write later when you want to restore the saved settings. In this section, we'll provide an example, both as an Access form (frmPTXMLData) and as a data access page (PTXMLData). Figure 12.15 shows the sample page, after it has restored a save layout.

Figure 12.15. The sample page allows you to save and restore pivot table layout settings (click picture for larger image).

The PivotTable control's XMLData property saves (in XML format) information about the control that allows the control to render itself just the same way, in a later session. The problem isn't directly related to the PivotTable control, but rather, to the data storage. That is, where are you going to put that XML data so it's available the next time your page opens? You have a number of choices, including a cookie, a text file, a Session variable, or, as we've done in this example, in a database table. Because your data access page is reading data from a database, it makes sense that you might be able to store data back to the same database. This example saves and retrieves data from a table (tblPivotTableLayout) set up expressly for this purpose. This table contains two fields: FormName (the Primary Key, containing the name of the form or page containing the pivot table) and XMLData (containing the actual XML data that you want saved and restored).

Because the PivotTable control retrieves its display data from a Data Source control in this example, we decided to use the Data Source control as a data source for the XML save and restore operations as well. Although you could open a separate connection to the data source and use it to save and restore the layout data, there's no reason to do so. In addition, using this technique gives us a chance to provide a single, simple example that demonstrates how you can programmatically manipulate the Office Web Component's Data Source control.

Note:   In this example, the name of the PivotTable control is pt. You'll see this name scattered about the sample code—in each case, the name refers to the PivotTable control on the sample page.

What You Need to Know about the Data Source Control

Among other things, the Data Source control acts as a "bucket" for recordset definitions and provides a collection of RecordsetDef objects. Each of these objects defines a recordset, but doesn't actually return rows. You can assign each of these RecordsetDef objects a name, just as you can with any item you add to a collection. Later, when you need to open the recordset (either to return rows, or to execute some action), you use the Execute method of the Data Source control.

Because of the way the Data Source control was written, it can be used as a data source for the Office PivotTable, Office Chart, and many other ActiveX controls. In addition, using the ElementExtension object provided by the control, Access can bind native HTML controls to the Data Source control as well. This example, however, isn't concerned with the data binding capabilities of the control. Instead, here you simply need code that can set up a RecordsetDef object and later execute it.

To create the RecordsetDef object, the window_onload event procedure calls the AddNew method of the RecordsetDefs collection of the Data Source control on the page (named MSODSC). The syntax for the Add method looks like this:

object.AddNew(Source, [RowsourceType], [Name])

where:

• object is the RecordsetDefs collection of the Data Source control.
• Source specifies the data source, just as the Source property might for an ADO recordset.
• RowsourceType optionally specifies the type of the new schema row source. It can be one of the dscRowsourceTypeEnum constants: dscTable (1), dscView (2), dscCommandText (3), the default value), dscProcedure (4), and dscCommandFile (5).
• Name optionally specifies the name of the recordset definition. If you do not specify this argument, the method creates a name for you. We suggest you always specify your own, distinct name.

In the window_onload event procedure for the page, the code calls the AddNew method of MSODSC, creating a new RecordsetDef object named "LayoutInfo":

Sub window_onload()
    MSODSC.RecordsetDefs.AddNew _
     "SELECT * FROM tblPivotTableLayout " & _
     "WHERE FormName = '" & document.title & "'", _
     MSODSC.Constants.dscCommandText, "LayoutInfo"
End Sub

This RecordsetDef, when you later execute it, will return the one row from tblPivotTableLayout matching this particular page. (Because of the way we created this table, each page in your application must have a unique title. Feel free to use some other field as the primary key in tblPivotTableLayout, if this doesn't serve your needs.) For now, all the code has done is create the named RecordsetDef object, for later use. For all intents and purposes, this is similar to creating a saved QueryDef object in an Access database: it doesn't itself return rows. However, when executed, it can run an action or return the requested rows. In later procedures, when the page needs to read or write from tblPivotTableLayout, it will call the Execute method of this RecordsetDef to create the necessary recordset.

Saving the XML Data

When you click the Save button on the sample page, the associated event procedure calls the adhSaveXMLData procedure, shown in Listing 12.11. This code calls the Execute method of the Data Source control, specifying the named RecordsetDef created in the window_onload event procedure. This code retrieves the row matching the title of the current page—if it exists. If there is no row (if rst.EOF is True), then the code adds a new row and sets the Primary Key field (FormName) to be the title of the current document.

The "active ingredient" of the procedure is the line of code that takes the XMLData property of the PivotTable control and places it into the XMLData field in the recordset. Once that's done, the code updates the recordset, and closes it.

Listing 12.11.

Sub cmdSave_onClick()
    Call adhSaveXMLData(pt)
End Sub

Sub adhSaveXMLData(pt)
    ' Given a PivotTable control, save
    ' the XMLData information to tblPivotTableLayout.
   
    Dim rst
    Set rst = MSODSC.Execute("LayoutInfo")
   
    If rst.EOF Then
        rst.AddNew
        rst.Fields("FormName") = document.title
    End If
   
    ' Save the XMLData property.
    rst.Fields("XMLData") = pt.XMLData
    rst.Update
       
    rst.Close
    Set rst = Nothing
End Sub

Retrieving the XML Data

Once you've saved a layout for the PivotTable control, you can retrieve it later. In this case, the example retrieves the layout information from the table where it was previously stored. To do that, the Restore button's onClick event procedure calls the adhRestoreXMLData procedure, shown in Listing 12.12.

This procedure is simpler than the adhSaveXMLData procedure, because this one only needs to retrieve the XML data if there is a matching row in the recordset, and do nothing if there is not. The code calls the Execute method of the Data Source control again, specifying the RecordsetDef created in the window_onload event procedure. This recordset should contain either one row or none. If the recordset contains a row, the code retrieves the XMLData field and assigns it to the XMLData property of the PivotTable control. That's all there is to it!

Listing 12.12.

Sub cmdRestore_onClick()
    Call adhRestoreXMLData(pt)
End Sub

Sub adhRestoreXMLData(pt)
    ' Given a PivotTable control, retrieve
    ' the XMLData information from tblPivotTableLayout.
   
    Dim rst
   
    Set rst = MSODSC.Execute("LayoutInfo")
    If Not rst.EOF Then
        pt.XMLData = rst.Fields("XMLData")
    End If
       
    rst.Close
    Set rst = Nothing
End Sub

Try It Out!

To demonstrate the behavior discussed in this example, load the sample page, modify its layout by dragging fields to the various drop areas, and then click the Save button. Close the page, and then reload it. Note that your changes have been lost, but not irretrievably! Click the Restore button, and after a moment of recalculation, your page should appear as you last saw it.

Note:   If you're deploying pages on a web server, you may not have write access to a convenient database in which to place your XML data. In that case, you might consider writing the data to a text file on the server or to a cookie on the user's machine.

There's much more to the Office PivotTable component than we've been able to show you here. As you'll find, if you dig in deeper, you can control almost every aspect of this complex and useful view mechanism. We suggest that you spend time with this control—of all the Office Web Components, it's the one you're most likely to want to use, again and again.

The Office Chart Component

The Office Chart component allows you to create customizable, programmable charts based on data from many different sources. Although this component looks as if it was based on the same charting engine as you'll find in Excel, it's actually a separate code base with similar functionality. The Office Chart component allows you to create most of the charts from Excel (including, in this release, the 3-D chart types), plus polar charts. In addition, the Office Chart control allows you to create multiple charts in the same chart space, giving you the capability of overlaying charts of the same or differing types. An interesting feature was added to this release of the Office Chart component: A PivotChart capability. PivotCharts provide the ability to do the same sort of dynamic analysis as PivotTables, but on the surface of a chart.

The Office Chart control can retrieve its data from any of these sources:

• Office Spreadsheet
• Office PivotTable
• Office Data Source control
• ADO recordsets
• Arrays containing literal values
• In this release, a datasheet stored with the chart itself, and displayed in the Commands and Options dialog box

In this section, you'll see examples binding charts to the Office Spreadsheet and PivotTable controls, as well as to an ADO recordset. In addition, one example will show how you can react to events raised by the Office Chart control as you move the mouse over the various elements of the chart.

Creating a Simple Bound Chart

To get you started, this section will walk through the process of creating a simple chart that's bound to an Office Spreadsheet control. The goal is to end up with a page that looks like Figure 12.16. This data access page exists in the sample database as Spreadsheet-Chart.

Figure 12.16. Follow the steps to create a chart bound to an Office Spreadsheet control (click picture for larger image).

Follow these steps to create the sample page:

1. Create a new data access page, in Design view. Type in a title, if you like.

2. On the new page, insert an Office Spreadsheet control. In the spreadsheet, add values as shown in Figure 12.16.

3. Add a new Office Chart control to the page, and place it underneath the existing Office Spreadsheet. Contrary to the previous release of the Office Chart component, the Microsoft Office Chart Wizard does not start at this point.

4. To supply the settings for the chart, right-click the control and select Commands and Options. Right-click the control again, and select Data. The Commands and Options dialog box will display the Data Source page shown in Figure 12.17.

5. Select the data source for the chart. In this case, your options are to choose from a datasheet, a database table or query, the data source control for the page, or the spreadsheet control on the page. (You see that particular option because the other control on the page is a spreadsheet. Were you to try this same experiment with a PivotTable control on the page, you'd see that control here instead.) Select the spreadsheet control, and click the Range button to choose the range to be charted.

   

   Figure 12.17. There are several options for the chart's data source.

6. On the Range page, you tell the wizard what data you'd like to display in the chart. Note (for later use) that you must supply, basically, three sets of information: one or more series, each of which can contain 0 or more values, and a set of categories. Enter A1:B4 in the Range text box, and click the OK button. The window should look like Figure 12.18, and a simple bar chart should be displayed.

   

   Figure 12.18. The component makes a guess at what the series and categories are for your chart, but you can change the settings if you like.

7. Select the Type tab in the Commands and Options dialog box, and choose a type you like for the chart. We chose the 3-D Column Clustered chart. You may want to resize the chart at this point to get a look you like.

8. Display the page in Page view (or, if you like, simply work with it in Design view—the controls are active either way) and change values in the spreadsheet. As you see, the controls are linked, and changes to the spreadsheet appear immediately in the chart.

Charting Terminology

As you saw when you built a chart yourself, you must supply a number of bits of information before the chart can draw itself correctly. In Figure 12.19, we've created a simple example to demonstrate the various parts of a chart. The pivot table shows data from qryOrderDetails, with a Sum of Quantity totals field added so you can see total sales of each item, per customer. The pivot table has been filtered to show only a few customers.

Note:   Figure 12.19 shows the sample page, GraphData. We'll discuss more about this example in the "Working with Chart Events" section later in the chapter. If you happen to move the mouse over the chart, you may notice some changes to the display of the chart. Don't worry: the "Working with Chart Events" section will explain what's going on.

Figure 12.19. This simple chart (bound to a pivot table) demonstrates series, categories, and values (click picture for larger image).

In general, you must specify the following information in order to create a chart:

Series

A chart series represents a related group of data points. Each series can have a different chart type, so you can overlay series with different types. (Of course, some combinations can't work: you can't overlay a pie chart with a line chart, for example.) Generally, every data point in a series displays the same color. In Figure 12.20, each different food category represents a different series.

Categories

Generally, categories are groups of data points made up of one data point from each data series in the chart. Not all charts display a category axis: bar and column charts do, but x-y plots don't. In the case of an x-y plot, the data points are defined by the two coordinates, not by some single category. In Figure 12.19, each city represents a different chart category.

Values

Chart values are, basically, the intersection of a category and a series. In Figure 12.19, each bar represents a value. On a pie chart, each wedge generally represents both a value and a category (that is, the category provides a label for the wedge, whose value is the percentage of the whole). On an x-y plot, each point represents two values (the x and y values). On a high-low-close chart, each point represents three values.

Axes

As you might imagine, axes mean the same thing here as they did when you learned about charts in high school. In Figure 12.19, cities are on the x (or horizontal, or categories) axis, and values are on the y (or vertical, or values) axis.

Creating and Manipulating a Chart Programmatically

To jump right in and demonstrate some of the scripting capabilities of the Office Chart component, the example in this section (the data access page named ChartFromRecordset) creates a pie chart based on data from a recordset. In addition, this example demonstrates how to modify layout properties of the chart. If you select the Animate the Chart check box, the chart will rotate, and the slices will move in and out from the center. Figure 12.20 shows the chart in mid-spin.

Tip:   Throughout this example, we'll refer to the top-level chart object as the ChartSpace object. The object has this name, rather than the more obvious Chart, because the top-level object could contain multiple charts. The ChartSpace object contains a collection of WCChart objects. If you have only a single chart displayed, it's Charts(0) within the ChartSpace object's collection of charts.

Figure 12.20. You can create this chart programmatically, using data from a recordset (click picture for larger image).

Getting started

The sample page creates its recordset in the window_onload event procedure, shown in Listing 12.13. In this procedure, the code first sets up a page-wide reference to the Constants property of the ChartSpace object:

Set c = owcChartSp.Constants

Then, the code uses the Data Source control's connection to open a recordset based on the query qryQuantityByCategory, which summarizes the quantity sold in each food category.

' Set up the recordset.
Set rst = CreateObject("ADODB.Recordset")
rst.Open "qryQuantityByCategory", MSODSC.Connection, adOpenStatic, _ adLockReadOnly, 
  adCmdTable

Finally, the procedure calls the FillPieChartFromRecordset procedure (discussed in the next section) to set up and display the pie chart, passing in the control and the recordset to work with, along with the name of the field providing the categories and the field supplying the values. (In this example, owcChartSp is the name of the Office Chart control.)

' Fill in the pie chart.
Call FillPieChartFromRecordset( _
 owcChartSp, rst, "Category", "SumOfQuantity")

Listing 12.13.

Sub window_onload()
   Dim rst
   Const adCmdTable = 2
   Const adOpenStatic = 3
   Const adLockReadOnly = 1
   Set c = owcChartSp.Constants

   ' Set up the recordset.
   Set rst = CreateObject("ADODB.Recordset")
   rst.Open "qryQuantityByCategory", MSODSC.Connection, adOpenStatic, _
    adLockReadOnly, adCmdTable ' Fill in the pie chart.
   Call FillPieChartFromRecordset( _
    owcChartSp, rst, "Category", "SumOfQuantity")
End Sub

Creating the chart from a recordset

Listing 12.14 shows the procedure, FillPieChartFromRecordset, that does all the work in this example, and the following paragraphs work through all the details of this procedure. Before you can display a bound chart (that is, a chart that's bound to some data source), you must set the DataSource property of the chart. You might use a Data Source control, a PivotTable control, a Spreadsheet control, or, as in this case, an ADO recordset. (In this example, owcChartSp is the name of the Office Chart control on the data access page.)

Set owcChartSp.DataSource = rst

Next, the code creates a new chart within the chart space:

Set owcChart = owcChartSp.Charts.Add()

The code must tell the chart how to display its data (it's a pie chart), and, in this case, to display a legend:

With owcChart
    .Type = c.chChartTypePie
    .HasLegend = True
End With

Now comes the important code: telling the chart from where to get its categories, values, and series. In the case of a pie chart, you'll only need to specify categories and values, and if you're binding the chart to a recordset, you must supply the names of the fields to use for both these items. To do that, you call the SetData method of owcChart. The syntax for the SetData method looks like this:

object.SetData(Dimension,DataSourceIndex, [DataReference])

where:

• object is an expression that returns an object for which you might set data. In this case, it's a WCChart object.
• Dimension specifies the data dimension to be set. For a WCChart object, it can be one of 12 different values, such as chDimSeriesNames, chDimCategories, chDimValue. See online help for a complete list.
• DataSourceIndex specifies the index of the selected data source. In this example, the chart has only a single data source, numbered 0. If you were to fill the chart with literal data, you would use the chDataLiteral constant instead of an integer.
• DataReference optionally supplies a data reference as a field name, or a data reference in Excel format ("A1:C16", for example). If you're supplying data as literal values, (that is, when the DataSourceIndex property is set to chDataLiteral), you can set DataReference to a one-dimensional array or a tab-delimited string.

Given the syntax for the SetData method, you can see how the FillPieChartFromRecordset procedure uses the field names you've supplied in order to set up the chart:

With owcChart

    ' Hook up the two necessary data fields.
    .SetData c.chDimCategories, 0, strCategoriesField
    .SetData c.chDimValues, 0, strValuesField
End With

In this example, the code sets the Category field to be the "categories" field, and the SumOf- Quantity field to be the "values" field. Once you've set those names, the chart can figure out how to display the data.

The remainder of the code works with properties of the data labels. To create data labels, you must first call the Add method of the DataLabelsCollection property of an item in the chart's SeriesCollection property.

Note:   To be completely correct, the chart's SeriesCollection property contains a collection of ChSeries objects. Each ChSeries object has a DataLabelsCollection property. This collection contains ChDataLabels objects, each of which describes the data labels for the series. (Based on the fact that a series has a collection of ChDataLabels objects, it should be possible to create multiple data labels for a given series. We haven't tried that.) The ChDataLabels object has a number of properties that the code in Listing 12.14 sets, to alter the display of the label for each pie slice.

The following code alters the data labels in these ways, setting the listed properties:

• Displays a percentage, and not the actual value of the slice (HasPercentage, HasValue).
• Displays the category name in the label (HasCategoryName).
• Sets the separator between the value and category name to be ": ", as opposed to the default "," (Separator).
• Sets the color for the interior to be white, and the border color to be black (Interior.Color, Border.Color).
• Sets the font of the label to Tahoma 8pt bold, in black (Font.Name, Font.Color, Font.Bold, Font.Size).

With owcChart
    ' Show a percentage, but not an absolute
    ' value. Also, show the category name.
    ' Use ":" as the separator between
    ' the category name and the percentage.
    With .SeriesCollection(0).DataLabelsCollection.Add
        .HasPercentage = True
        .HasValue = False
        .HasCategoryName = True
        .Separator = ": "

        ' Use 8pt black text on a white background,
        ' in Tahoma font.
        .Interior.Color = "white"
        .Border.Color = "black"
        With .Font
            .Name = "Tahoma"
            .Color = "black"
            .Bold = True
            .Size = 8
        End With
    End With
End With

That's all it takes to create the chart shown in Figure 12.21.

Listing 12.14.

Sub FillPieChartFromRecordset( _
 owcChartSp, rst, _
 strCategoriesField, _
 strValuesField)
' Given a chart control, pull data from the
' specified recordset.

   Dim owcChart
   Set owcChartSp.DataSource = rst

   ' The component creates a chart at this point, but we
   ' can't seem to change its properties programmatically.
   ' So we'll create another and work with it...

   Set owcChart = owcChartSP.Charts.Add()
   With owcChart
     .HasLegend = True
     .Type = c.chChartTypePie
     ' Hook up the two necessary data fields.
     .SetData c.chDimCategories, 0, strCategoriesField
     .SetData c.chDimValues, 0, strValuesField

      ' Show a percentage, but not an absolute
      ' value. Also, show the category name.
      ' Use ":" as the separator between
      ' the category name and the percentage.
      With .SeriesCollection(0).DataLabelsCollection.Add
         .HasPercentage = True
         .HasValue = False
         .HasCategoryName = True
         .Separator = ": "

         ' Use 8pt black text on a white background,
         ' in Tahoma font.
         .Interior.Color = "white"
         .Border.Color = "black"
         With .Font
            .Name = "Tahoma"
            .Color = "black"
            .Bold = True
            .Size = 8
         End With
      End With
   End With

   ' For some reason, a chart appears as soon as
   ' we set the data source, but we can't change its
   ' properties programmatically. So we added a new one and
   ' now must get rid of the first chart if it exists.

   if owcChartSP.Charts.Count > 1 then
      owcChartSP.Charts.Delete(0)
   end if
End Sub

Note:   If you care to investigate, you'll find a similar example in the form frmChartFromRecordset. The concepts are the same, but the code's written in VBA instead of in VBScript.

Rotating the chart

If you care to dig into it, you might find the RotatePie procedure, in the sample source code, interesting. This procedure, called every 200 milliseconds, modifies both the FirstSliceAngle property of the chart, and Explosion property of the first SeriesCollection object. The procedure uses a bit of tricky math to do its work. Each time it's called, the procedure increments the FirstSliceAngle by 20 degrees, but you can't keep incrementing this value—once it gets past 360, the chart raises an error. Therefore, the code uses the MOD operator to ensure that the value for the angle is between 0 and 359. (The MOD operator returns the remainder you get when you divide a the first operand by the second. For example, 340 MOD 360 is 340, but 360 MOD 360 is 0, and 380 MOD 360 is 20.) To handle the Explosion property (a value between 0 and 1000, indicating the percentage of the chart's radius where the tips of the slices are drawn), the code attempts to increase the property from 0 to 200, and then decrease back to 0 again, over and over. Although there are several ways to solve this problem, the code uses the Sin function, which provides values between –1 and 1, depending on its input. Given that value, the procedure multiplies the return value from the Sin function by 100, and adds the result to 100. This ends up with a value between 0 and 200, in a repeating pattern. If you're into high-school trig, have fun. Otherwise, just note that the RotatePie procedure does all the work of making those little sections move and explode.

Another issue, in the data access page version of this example, is how you make something happen at regular intervals. (On the Access form example, we've simply added the code to the form's Timer event.) For a data access page, you can use the setInterval and clearInterval methods of the window object to specify a function to be called at regular intervals. See the window_onload event procedure in the sample page to see how it works.

Working with Chart Events

Among the many other programmable features supplied by the Office Chart control, you're likely to find its capability for reacting to mouse events a measurable benefit over the static nature of charts created with Microsoft Chart. As you'll see in this section, you can have a chart react to MouseOver and Click events, among others, so that your users can interact with the chart in ways that simply weren't possible in previous versions of Access.

Tip:   Although this example uses a data access page to demonstrate the event functionality, you can react to the same events from an Access form.

Figure 12.21 shows the demonstration page, GraphData, discussed earlier in the chapter. Give it a try in Page view again—except this time, note that as you move your mouse over the bars of the chart, both the border of the "selected" bar and the title of the chart change to reflect the selection. In addition, if you click on one of the series displayed in the legend, the chart will select all the values in the series, and display the total for the food type in the chart title.

Figure 12.21. You can react to mouse movement and clicks on a chart by using event procedures (click picture for larger image).

Reacting to events

In this example, the code for the page reacts to the MouseMove and MouseDown events of the Office Chart object. That is, you'll find owcChart_MouseMove and owcChart_MouseDown event procedures in the script associated with the GraphData data access page. Based on where the mouse is when the events occur, the code takes specific actions. Therefore, two questions might occur to you:

• How does the event procedure know where the mouse was when the event occurred? The code provides you with x and y coordinates of the location where the mouse was when the control raised the event. How does the control pass this information to your event procedure?
• How does the code convert coordinates into specific data points or chart elements on the screen? That is, how can you know what chart element you moved over, or clicked on, based on x and y coordinates?

The Office Chart control provides an elegant answer to the first question. If you think about how VBA handles event procedures, you'll remember that for different event procedures, VBA passes different parameters depending on the event. For example, the Form_Open and Form_ Unload event procedures receive a single parameter named Cancel, the Form_KeyPress event procedure receives a single integer indicating the key that was pressed, and the Form_KeyDown event procedure receives two parameters (the keycode and the shift state). This works fine in VBA, where the editor supplies the parameters and their types for you. When you're writing script, however, the editor can't supply this information: it's up to you to create the correct procedure signature when you write the code. Check out the owcvba10.chm help file for the parameters passed for each event. For example, the chart's MouseMove and MouseDown event procedures include parameters for the x and y coordinates. We'll use those to determine where the user clicked.

The next question is a bit tougher. That is, given an x and y coordinate within a chart, how can you tell what chart object, exactly, is underneath the mouse? To solve this problem, the Office Chart control provides the RangeFromPoint method: you supply x and y coordinates, and it returns the object underneath that coordinate.

This leads, unfortunately, to another question. The chart is made up of a number of different types of objects (values, legend entries, axes, blank spaces, and so on). How can you tell what type of object has been selected? To solve this, you can use the TypeName function, built into VBScript. This function returns the object type corresponding to the chart object that's under the mouse (that is, the return value from the RangeFromPoint method). In this example, the code looks for objects of type ChPoint (data points) or ChLegendEntry (legend entries). Once you know what type of object you're dealing with, your code can make intelligent choices about what to do.

As an example, take a look at the MouseMove event procedure for the chart, shown in Listing 12.15. This code declares an object (objSelection) and sets this object to refer to the return value from the RangeFromPoint method. As you can see, the code passes the EventInfo.x and EventInfo.y properties as parameters to RangeFromPoint, where EventInfo was the object passed as a parameter to the event procedure. If the TypeName function, passed objSelection, returns the text "WCPoint," the code knows that it's found a data point and calls the SelectItem method (discussed in the next section) to highlight the item.

Listing 12.15.

Sub owcChart_MouseMove(Button, Shift, x, y)
   Dim objSelection
   Set objSelection = owcChart. _
    RangeFromPoint(x, y)
   If TypeName(objSelection) = "ChPoint" Then
      Call SelectItem(objSelection)
   End if
   Set objSelection = Nothing
End Sub

The MouseDown event procedure, shown in Listing 12.16, does much the same thing, but this procedure looks for legend entries: objects of type "ChLegendEntry." If that's what it finds, it sets objSelection to be the entire series collection associated with the legend entry (using the SeriesCollection property of the chart) and then calls the SelectItem method to select the entire series.

Note:   The Click event seems like a more appropriate choice for this bit of code, but the Click event of the Chart object doesn't receive any parameters. We've used MouseDown because we need to determine the mouse's x and y coordinates.

Listing 12.16.

Sub owcChart_MouseDown(button, shift, x, y)
   Dim objSelection
   Set objSelection = _
    owcChart.RangeFromPoint(x, y)
   If TypeName(objSelection) = "ChLegendEntry" Then
      Set objSelection = owcChart.Charts(0). _
       SeriesCollection(objSelection.Index)
      Call SelectItem(objSelection)
   End If
   Set objSelection = Nothing
End Sub

Working with the selection

When the code calls SelectItem (shown in its entirety in Listing 12.17), previous event procedures have either sent it a single point (with the type name "ChPoint") or an entire series (with the type name "ChSeries"). No matter which type of object SelectItem receives, its job is to reset the appearance of the last item selected so that it has a thin black border, and then make the currently selected object have a heavy red border. The code makes use of a page-level variable, ptLastSelection, which keeps track of the previously selected point (or series). The procedure starts out with this simple chunk of code:

If Not (ptLastSelection Is Nothing) Then

    With ptLastSelection.Border
        .Weight = 1
        .Color = "black"
    End With
End If     
With objSelection.Border
    .Weight = 5
    .Color = "red"
End With
Set ptLastSelection = objSelection

Tip:   As you may have noticed, the chart doesn't care if you're setting the border properties of a single point or an entire series. Either way, you simply set the appropriate properties. The chart takes care of assigning the weight and color to the group of objects, if necessary.

Next comes the tricky part: as you move the mouse around, or click on the legend entry, the chart's title changes to indicate the value or series you've selected. To do this work, the code needs to be able to retrieve a value from a chart point (or add up all the values for an entire series). The chart control makes this possible, supplying the GetValue method of a ChPoint object. The syntax for GetValue looks like this:

object.GetValue(Dimension)

where:

• object is an expression that returns a WCPoint object.
• Dimension specifies the type of data to be returned for the specified point. It can be one of the enumerated values in the ChartDimensionsEnum type, such as chDimXValues, chDimYValues, chDimSeriesNames, chDimCategories. See the VBA Object Browser or online help for a complete list.

If you select a single point, the code builds up a string (such as "Pizza – Total in Redmond: 91") using the series name ("Pizza"), the category name ("Redmond") and the value ("91"). If you select an entire series, the code adds up the values for all the points in the series and creates a string (such as "Soup – Total: 234). This time the code uses the series name ("Soup") and the total of the values ("234"). In each case, the procedure calls the GetValue method of the selected point (or points).

Select Case TypeName(objSelection)
    Case "ChPoint"
        strSeries = _
         objSelection.GetValue(c.chDimSeriesNames)
        strCategory = " in " & _
         objSelection.GetValue(c.chDimCategories)

        ' Get the single value.
        varValue = objSelection.GetValue(c.chDimValues)
    Case "ChSeries"
        strSeries = objSelection.Name
        strCategory = ""

        ' Add up all the values
        varValue = 0
        For Each pt in objSelection.Points
            varValue = varValue + _
             pt.GetValue(c.chDimValues)
        Next           
    Case Else
        ' In case you add more click locations later.
End Select

The procedure ends by setting the Caption property of the chart's Title object to be the strings the code has created earlier in the procedure:

owcChart.Charts(0).Title.Caption = _
 strSeries & strCategory & ": " & CStr(varValue)

Listing 12.17.

Dim ptLastSelection

Sub SelectItem(objSelection)
    Dim varvalue
    Dim strCategory
    Dim strSeries
    Dim pt 
   
    On Error Resume Next
    If Not (ptLastSelection Is Nothing) Then
        With ptLastSelection.Border
            .Weight = 1
            .Color = "black"
        End With
    End If     
    With objSelection.Border
        .Weight = 5
        .Color = "red"
    End With
    Set ptLastSelection = objSelection

    Select Case TypeName(objSelection)
        Case "ChPoint"
            strSeries = _
             objSelection.GetValue(c.chDimSeriesNames)
            strCategory = " in " & _
             objSelection.GetValue(c.chDimCategories)
           
            ' Get the single value.
            varValue = objSelection.GetValue(c.chDimValues)
        Case "ChSeries"
            strSeries = objSelection.Name
            strCategory = ""

            ' Add up all the values
            varValue = 0
            For Each pt in objSelection.Points
                varValue = varValue + _
                 pt.GetValue(c.chDimValues)
            Next           
        Case Else
            ' In case you add more click locations later.
    End Select
    owcChart.Charts(0).Title.Caption = _
     strSeries & strCategory & ": " & CStr(varValue)
    On Error Goto 0
End Sub

Take the time to dig into the mechanics demonstrated in this example. It's so easy to react to events of the Office Chart control that you'll be able to add useful interactivity to your charts, both on data access pages and on Access forms, with very little effort.

Tip:   For information on creating and using the Office Chart control on a web server, see Chapter 14.

Summary

Hopefully, you've found enough information in this introduction to using and programming the Office Web Components to get you started. We haven't made any attempt to be complete, nor comprehensive, in this coverage—there's simply not enough room for that. We've supplied a few examples and shown you a good deal of code. If you want to dig deeper, you'll want to start by looking at the help files supplied with Office XP. Once you've worked through those, we suggest the book mentioned earlier in the chapter:

Programming Microsoft Office 2000 Web Components, Dave Stearns, Microsoft Press ISBN: 0-7356-0794-X.

This chapter's intent was to introduce you to the various Office Web Components: the Office Spreadsheet, Office PivotTable, and Office Chart controls, all derived from Excel functionality. In addition, both in this chapter and in Chapter 10, we used the Data Source control (also part of the Office Web Components) to provide data binding and to act as a container for recordset definitions. Given the material here, and the examples provided, you have enough material to keep you busy for some time and to get you started using these new additions to Microsoft Office.

The Office Web Components work, they work quickly, they work well, and they're lightweight. If you're distributing standard Access applications, you can use them on Access forms. If you need to create server-side pages, using ASP, they work well created as COM components on the server. If you're creating web applications where you can guarantee that your users run Internet Explorer 5 or above, the Office Web Components make a great addition to your programming arsenal.