Microsoft Office 2000 Web Components Basics

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.

 

Kevin McDowell
Microsoft Corporation

Created: March 2000
Revised: January 2001

Applies To: Microsoft Office 2000

Summary: This article discusses publishing the Microsoft Office Web Components from Microsoft Excel and working with them in several environments: Microsoft Internet Explorer, the Visual Basic Editor, Microsoft FrontPage, and Microsoft Access. (42 printed pages)

Download Msowcbasics.exe.

Contents

Overview
Publishing the Components from Microsoft Excel
Working with the Components in Microsoft Internet Explorer
Working with the Components by Using Microsoft Visual Basic
Working with the Components in Microsoft FrontPage
Using Microsoft Office Web Components in Data Access Pages
Conclusion

Overview

The Microsoft® Office Web Components were shipped for the first time in Microsoft® Office 2000. They are a collection of ActiveX® controls used for adding spreadsheet, chart, and data-manipulation functionality to Web pages. When you use Microsoft® Internet Explorer to browse a Web page that contains an Office Web Component, you can manipulate the displayed data right in Internet Explorer by sorting and filtering the data, entering new values, expanding and collapsing details, and rotating rows and columns to see different summaries of the source data. All of this functionality is provided by the controls.

Because the Office Web Components are fully programmable, you can use them to build rich, interactive, Web-based solutions in many design environments, including Microsoft® FrontPage®, Microsoft® Access data access pages, and Microsoft® Visual Basic®. You can also publish the Office Web Components directly from Microsoft® Excel with minimal design setup.

There are three Office Web Components—the Spreadsheet Component, the Chart Component, and the PivotTable® Component. Each component has an associated ActiveX control (Spreadsheet, Chart, and PivotTable List) and object (Spreadsheet, ChartSpace, and PivotTable). In addition to the Office Web Components and their associated ActiveX controls, there is a fourth ActiveX control, the Data Source control, that is used to bind the other components to a data source; this control is used extensively by data access pages in Access. When it is published, the Data Source control is not visible to someone viewing the page in Internet Explorer. Like the other ActiveX controls, the Data Source control has an associated object, the DataSourceControl object. The Office Web Components and the Data Source control all ship in the same DLL (Msowc.dll).

After introducing the components, I will discuss publishing them from Excel, then discuss the browser experience, and finally move to working with the components in Visual Basic forms, in Internet Explorer by using Microsoft Visual Basic Scripting Edition (VBScript®), and in FrontPage Web pages and Access data access pages. A sample file, OWCExamples.xls, is included in the MSOWCBasics.exe download and is used to demonstrate publishing from Excel; it also contains Visual Basic for Applications (VBA) code examples.

It is assumed that the reader has a moderate understanding of HTML, VBA, VBScript, Access, and the FrontPage design environment.

Requirements

The Office Web Components require Microsoft Internet Explorer version 4.01 or later running on the Microsoft® Windows® 95, Microsoft Windows 98, Microsoft Windows NT® version 4.0, or Microsoft Windows 2000 operating system. Hardware requirements are 16 MB of RAM and any Intel 486, Pentium, or Intel-compliant processor, or any DEC Alpha processor. To design a component-based page with Access, or to browse a page created with Access, you must have Microsoft Internet Explorer 5 or later to experience full functionality in the browser. If you are using an earlier version of Internet Explorer to view pages published from Access, you can view the data presented by the components, but you cannot work with them interactively in the browser. Users must have an Office 2000 license in order to browse a Web page interactively with the Office Web Components. The user's experience when he or she doesn't have a license is covered in the "Publishing Without the Components Installed" section later in this article.

The Spreadsheet Component

The Spreadsheet Component is designed to provide Excel-like functionality within Internet Explorer. The Spreadsheet Component allows you to take an entire spreadsheet, a range of cells, or a named range (Print Area, AutoFilter, or a user-defined name) and publish it to a Web page, as shown in Figure 1. When the data is published, the formulas are maintained so that users can modify the data in the Web page and see the results of changes made to the formulas or data in the browser. Note that you can also publish PivotTable reports with the Spreadsheet Component. However, the reports do not have PivotTable functionality when they are published this way.

Figure 1. The Spreadsheet Component as it appears in Internet Explorer

When a range of cells that contain data is published from Excel, the Spreadsheet Component is automatically populated with that data. You can also programmatically populate the Spreadsheet control with data.

The Spreadsheet Component provides the following functionality in Internet Explorer:

• Support for most functions found in Excel 2000
• Recalculation engine
• Simple spreadsheet user interface
• Basic spreadsheet formatting
• Data sorting
• AutoFilter
• Multi-level undo

The Chart Component

The Chart Component is a data-bound ActiveX control that is used by Microsoft Office to add charting functionality to Web pages. When it is published from Excel, as shown in Figure 2, the resulting Web page includes not only the Chart Component but also the Spreadsheet or PivotTable Component, depending on where the chart's data was located in the spreadsheet—in a range or in a PivotTable report. When the Chart Component is viewed in Internet Explorer, changes made to the data in the Spreadsheet or PivotTable Component automatically appear in the Chart Component.

Figure 2. The Chart Component as published from Excel

When published from Microsoft Excel, the Chart Component is automatically bound to the Spreadsheet or PivotTable Component that contains its data. However, you can also either programmatically bind the Chart Component to a data source or you can provide the data to be charted within the code.

The PivotTable Component

The PivotTable Component allows users to analyze data by adding sorting, grouping, filtering, outlining, and other data-manipulation functionality to Web pages. This component combines the list features of Excel (sort, AutoFilter, and outline) with the auto-summarizing features of PivotTable reports into a single ActiveX control that runs in Internet Explorer. Therefore, a PivotTable list on a Web page is roughly equivalent to a PivotTable report in an Excel worksheet.

The source data for a PivotTable list can come from an Excel spreadsheet, any tabular OLE DB or ODBC data source, or any OLAP data source supported by the Microsoft OLE DB Provider for OLAP Services. The PivotTable Component provides the following functionality:

• Cross-tabulation (the ability to rotate the rows and columns to see different summaries of the source data)
• Browse report data
• Dynamically filter (AutoFilter, Filter by Selection, Filter fields) and sort
• Group by row or by column
• Create totals
• Work efficiently with large or small amounts of data
• Drill into the details behind totals (works only with tabular data sources, not OLAP data sources)

In order to understand how PivotTable reports and lists work, you should be familiar with the following areas, as shown in Figure 3:

• Drop area   Any area where you can drop fields to be used in the PivotTable list.
• Filter area   The top drop area of the PivotTable list. Used for filtering the entire PivotTable list. A drop-down checklist of all items in the filter field is provided. Selecting a specific item allows only that item to be displayed in the Data/Detail area.
• Row area   The left drop area. Used for row groupings.
• Column area   The area below the Filter area. Used for column groupings.
• Data area   The main body of the PivotTable list. Used for all totals (Sum, Count, Min, Max) and lists.
• Detail area   Also in the main body of the PivotTable list. Used for fields that supply detail (that is, no total has been applied). You display this area by clicking the Expand/Collapse button on the PivotTable Component toolbar.

Figure 3. An unpopulated PivotTable List control showing the Filter, Column, Row and Totals/Details areas

The PivotTable Component does not support calculated fields. When a PivotTable report is published from Excel, any calculated fields are dropped out of the published range.

The Data Source Control

The Data Source control (DSC), although a powerful control, is discussed here only in relation to the other Microsoft Office Web Components. This control provides data retrieval, data binding, and cursor management functionality for the Office Web Components. The Data Source control is published from Microsoft Excel only when a chart or PivotTable report is published and those components need to be bound to data. The Data Source control itself is not visible in Internet Explorer, and there are no options to publish to it from within Microsoft Excel, Microsoft FrontPage, or Microsoft Access. It is used extensively by Access for data access pages.

Using the Components Across Domains

The Microsoft Office Web Components can be used on intranets as well as the Internet. When you access them across an intranet in the same domain, they work properly. However, when you attempt to access data in another domain, you may receive security warnings. For example, if the page you are viewing is in the domain https://www.microsoft.com and the data you are accessing is on the domain https://www.msn.com, unless https://www.msn.com is trusted by https://www.microsoft.com, you will receive the following security warning:

"This page accesses data on another domain. Do you want to allow this? To avoid this message in Internet Explorer, you can add a secure Web site to your Trusted Sites zone on the Security tab of the Internet Options dialog box."

This warning is supposed to appear because it protects you from malicious scripting attempts. When you add that domain to your Trusted Sites zone, you will no longer receive the warning when accessing data from that site. You can adjust this particular security option (Access data sources across domains) in the Internet Explorer 5 Security Settings dialog box. To access this dialog box, click Options on the Tools menu, and then click the Security tab. Note that Internet Explorer 4 uses the Script ActiveX Controls marked safe for scripting setting to control the cross-domain warning.

**Note   **This is the simplest way to address the problem of unauthorized data access from malicious scripts, but this approach assumes that you trust all users who are allowed to publish to servers or file shares in the Trusted Sites zone, or that an administrator examines the script behind all pages that contain data-bound Office Web Components before publishing them, to make sure that they don't contain malicious scripts. For a broader look at security issues related to data access pages, see "Deploying Data Access Pages on the Internet or Your Intranet."

When you set the Data Source control's UseRemoteProvider property to True, the Data Source control adjusts the connection string to use Remote Data Services and point back to the Web server from which the page came. This approach also avoids the cross-domain data access warning.

Using the Microsoft Office Web Components

There are five basic ways to create the Office Web Components:

• Select a range of cells or a chart, or create a list or PivotTable report in Excel and publish it or save it as an interactive Web page. You will be given the option to save to the appropriate component (Spreadsheet, Chart or PivotTable).
• Add one of the components to a VBA UserForm, a Visual Basic form, or an Access form.
• Add one of the components to a data access page in Microsoft Access.
• Create a Web page in Microsoft FrontPage and insert any of the Office Web Components from the Component menu.
• Insert the components into a project within the Microsoft® Visual InterDev® environment.

Publishing the Components from Microsoft Excel

The Office Web Components are created from Microsoft Excel when you do one of the following:

• Click Save as Web Page on the File menu, select the Add Interactivity check box in the Save As dialog box, and then click the Save button.
• Click Save as Web Page on the File menu, click the Publish button in the Save As dialog box, select the Add interactivity with check box in the Publish as Web Page dialog box and then select which functionality you want, and then click the Publish button.

Both of these options do the same thing: publish Excel data as a Web page so that users can modify or change the data within Internet Explorer. The item you have selected in Excel determines what component will be used when the data is published, as shown in the following table.

Selected item in Excel	Microsoft Office Web Component used for publishing
Sheet, individual cell, range of cells, Advanced Filter list, print area	Spreadsheet
PivotTable report, external data range (query table), list filtered with AutoFilter	PivotTable
Chart or PivotChart® report	Chart (with the source data appearing in either a Spreadsheet or PivotTable component)

The Publish as Web Page Dialog Box

When you click Save as Web Page on the File menu in Microsoft Excel, you can either publish the entire workbook (statically) or the current selection (with or without interactivity). If you click the Publish button in the Save As dialog box, the Publish as Web Page dialog box is displayed, which gives you more options for saving an .htm file. When this dialog box is displayed, whatever item is currently selected is displayed in the Choose list. For example, in Figure 4, a range of cells was selected in the Excel worksheet.

Figure 4. The Publish as Web Page dialog box

However, you are still able to select what you want to save. Excel identifies the available objects that can be published and lists them in the Choose box. In Figure 5, the Choose list shows the default list of items that you can publish from this workbook.

Figure 5. List of items that can be published

If you select Items on Sheetname, a list of all items on that sheet is displayed, as shown in Figure 6.

**Note   **When Items on Sheetname is selected, the only named ranges from the sheet that are displayed in the Choose list are print areas and AutoFilter lists. If you select Range of cells in the Choose list (instead of Items on Sheetname), you can manually enter a named range in the cell reference edit box in the format =DefinedName.

Figure 6. The list that is shown when Items On Sheetname is selected

When you publish the data, you are prompted for a file name. If you attempt to publish to an existing file, you will be prompted to replace or add to the existing file. If you choose to add to the existing file, the components being published will appear at the bottom of whatever information is already in the file.

Republishing an Item

In Figure 5 above, note that the first item in the Choose list is Previously published items. This option is available only when you have already published something from the workbook. When you select that option, the Choose list displays all items previously published from the current workbook. The workbook stores all the information necessary to republish a previously published item. Therefore, you can publish a specific item multiple times without having to reset anything.

Publishing Without the Components Installed

To browse a Web page interactively with the Office Web Components, you must have a Microsoft Office 2000 license. If you obtained a copy of Excel outside the Microsoft Office package, you can publish interactively from Excel but will not be able to view the resulting Web page because the components do not ship with stand-alone copies of Excel. Although the option to publish interactively is enabled in the stand-alone and Microsoft Office Small Business versions of Excel, you still can't view a page output with interactivity without the Office Web Components.

When you publish a Web page that uses Office Web Components, the page includes the capability to start a Web-based installation on the user's computer. In the source code for the page, the following line is added to the page as part of an object definition:

codebase="file:\\installationpath\msowc.cab#version=version"

The codebase path points to the Office 2000 installation point on a network server, or to a location specified in the Windows registry or on the Files tab of the Web Options dialog box (Tools menu, Options command, General tab) in Excel.

There are two possible results in Internet Explorer when users view a Web page that contains an Office Web Component on a machine that does not have the components installed:

• If you use the codebase attribute or the Web Options dialog box to specify a server location from which to download the Office Web Components, the graphic shown in Figure 7 is displayed in Internet Explorer.

  

  Figure 7. Graphic displayed when the Web Components have not been installed

  In addition to the graphic shown in Figure 7, the dialog box shown in Figure 8 is displayed.

  

  Figure 8. Microsoft Office Web Components Installer prompt

  If users click Yes, they can install the Microsoft Office Web Components and view the data in the components.

  **Note   **To automatically install the Office Web Components when opening a Web page, the user opening the page must be able to establish secure access to the Windows network file share (such as \\MyServer\MyShare) where the installation files reside. The Office Web Components cannot be automatically installed across an HTTP, HTTPS, or FTP connection. For this reason, users can't automatically install the Office Web Components directly from the Web server (or any other HTTP or FTP address) when they are viewing a Web page that is opened directly from the Internet. However, if a user has previously installed Office 2000, which includes installation of the Office Web Components, he or she will be able to open and use data access pages. Additionally, if the user has an Office 2000 license and doesn't have the Office Web Components installed, but does have a local network installation point configured in the registry, the user will be able to automatically install the Office Web Components when opening a data access page that is opened directly from the Internet.

• However, if no server is specified in the codebase attribute, Internet Explorer displays the following text, where "Microsoft Office Web site" is a link to the Microsoft Office Update Web site:

  "To use this Web page interactively, you must have Microsoft Internet Explorer 4.01 or later and the Microsoft Office Web Components. See the Microsoft Office Web site for more information."

Publishing Examples

The following examples make use of the OWCExamples.xls file included with this article.

Spreadsheet Example

To publish the spreadsheet example, do the following:

1. Open the OWCExamples.xls file.
2. Select the OWCSS worksheet.
3. Select cell $B$6 and try to change the data. Because the sheet is protected, you shouldn't be able to modify $B$6.
4. Select the range $A$1:$B$6.
5. On the File menu, click Save as Web Page.
6. In the Save As dialog box, click Selection: $A$1:$B$6.
7. Select the Add Interactivity check box.
8. In the File Name box, type OWCSS.htm, and then select a location to save to.
9. Click Save.

Open OWCSS.htm in Internet Explorer. Change any of the mortgage information. Does the monthly payment change? It should. Note that you cannot change the formula in the Monthly Payment cell. Because that cell was protected in Microsoft Excel, when the data is published, it cannot be modified in Internet Explorer either. To view help about the Spreadsheet Component when you are in Internet Explorer, click the Help button on the Spreadsheet Component's toolbar.

Chart Example

To publish the chart example, do the following:

1. In the OWCExamples.xls file, select the OWCChart worksheet.
2. Click the chart once to select it.
3. On the File menu, click Save as Web Page.
4. In the Save As dialog box, click Selection: Chart.
5. Select the Add Interactivity check box.
6. In the File Name box, type OWCChart.htm, and then select a location to save to.
7. Click Save.

Open OWCChart.htm in Internet Explorer. Note that both the Chart and Spreadsheet components are visible. When you change data in the Spreadsheet Component, note that the Chart Component updates appropriately.

PivotTable List Example

To publish the PivotTable list example, do the following:

1. In the OWCExamples.xls file, select the OWCPT worksheet.
2. Select the range $E$1:$G$31. Because it is a PivotTable report, you will have to hold down the SHIFT key and then use the arrow keys to select the range.
3. On the File menu, click Save as Web Page.
4. In the Save As dialog box, click the Publish button.
5. In the Choose list, click Items on OWCPT.
6. Underneath the Choose list, click PivotTable3($E$1:$G$31).
7. Select the Add interactivity with check box. It should automatically default to PivotTable functionality.
8. In the File Name box, type OWCPT.htm, and then select a location to save to.
9. Click the Publish button.

Open OWCPT.htm in Internet Explorer. Note that the PivotTable list contains data. To view PivotTable Component help, click the Help button on the PivotTable Component's toolbar.

Working with the Components in Microsoft Internet Explorer

Now that we have some examples to work with, let's take a look at what we get in Internet Explorer. Of the three components, only the Spreadsheet and PivotTable components provide a way for users to interact with the component when it is displayed in Internet Explorer. Both of them use a toolbar and the Property Toolbox to allow browser interaction and formatting. The following sections briefly discuss these run-time user interfaces.

Understanding the Spreadsheet Component's User Interface

The Spreadsheet Component toolbar (shown in Figure 9) is a scaled-down version of the Standard toolbar in Excel. Although you can turn the toolbar off, you cannot disable or hide individual buttons on the toolbar.

Figure 9. The Spreadsheet Component toolbar

Many of the buttons in the Spreadsheet Component toolbar share the same basic functionality as their counterparts in Excel. The commands, with the exception of Help, are also available from the spreadsheet grid when you right-click the grid to open the shortcut menu. The differences in behavior between functionality in Excel and the Spreadsheet Component are noted in the following table.

Buttons	Behavior in Spreadsheet Component
Undo	Can undo the following operations: sorting, applying a filter, remove a filter, applying a number format, changing a cell's value, pasting, changing the font formatting (font style, size, bold, italic, underline, color), changing the cell formatting (fill, alignment), clearing a cell, and inserting a column or row.
Cut, Copy, Paste	Supports cutting, copying, or pasting HTML and text to the Clipboard, but does not support cutting, copying, or pasting BIFF, RTF, or other formats. Always copies to the operating system Clipboard. Unlike in Excel, intervening actions between the cut, copy, and paste operations are allowed. This means you don't have to immediately paste a cell after you copy it.
AutoSum	Functions the same as in Excel.
Sort Ascending, Sort Descending	Functions the same as in Excel. The shortcut menu command cascades to a Sort menu that lists field headings to sort by. There is no Sort dialog box as in Excel.
AutoFilter	Contains check boxes in a drop-down list so that multiple items can be selected. There are no Top 10 or Custom options. You cannot use the Advanced Filter feature.
Export to Excel	Opens Microsoft Excel and copies all the data from the Spreadsheet Component to a new, read-only worksheet that has a name in the format of OWCSheet#####.htm where ##### is a random number. There are 65,536 rows as in Excel, but there are 676 columns going from A to ZZ, instead of the 256 columns available in Excel. If the extra columns are used and the data is exported back to Excel, the data in the extra columns is lost.
Property Toolbox	Displays the Spreadsheet Component's Property Toolbox. For more information about the Property Toolbox, see "The Property Toolbox" later in this article.
Help	Displays Microsoft Spreadsheet Help.

Understanding the Chart Component's User Interface

There is no interface exposed at run-time for the Chart Component. There is only one option on the shortcut menu, About, which displays a standard About Microsoft Office Web Components dialog box, and no toolbars or toolboxes. Users can manipulate the chart at run-time by changing the data in the chart's data source (a Spreadsheet or PivotTable Component) if it is visible. The Chart Component includes a Chart Wizard, but the wizard cannot be activated except in an appropriate design environment. This wizard is discussed in the "Working with the Components in FrontPage" section later in this article.

Understanding the PivotTable Component's User Interface

The PivotTable Component also makes use of a toolbar and the Property Toolbox. The PivotTable Component toolbar (shown in Figure 10) contains some of the same basic functionality as the PivotTable toolbar in Excel, but it has some additional controls.

Figure 10. The PivotTable Component toolbar

The following table outlines what the controls on the PivotTable Component toolbar do.

Control	Control type	Action
About	Button	Displays About Microsoft Office Web Components dialog box.
Copy	Button	Copies selected data to the Clipboard.
Sort Ascending, Sort Descending	Toggle	When turned on, sorts column or row fields in ascending or descending order. When turned off, data reverts to the way it was saved. Unlike PivotTable reports in Excel, blanks sort to the top.
AutoFilter	Toggle	When turned on, hides or shows items according to whether their check boxes have been cleared or selected in the Column or Row fields drop-down list. When turned off, all items are displayed.
AutoCalc	Menu	Gives you a choice of summarizing fields by using the Sum, Count, Min, or Max functions. Can be disabled by the creator of the PivotTable list.
SubTotal	Toggle	Shows or hides subtotals for selected field.
Move to Row Area	Button	Moves selected column field to row area (selection becomes a row field).
Move to Column Area	Button	Moves selected row field to column area (selection becomes a column field).
Move to Filter Area	Button	Moves selected field to Filter field area (selection becomes a filter). Can be restricted by the creator of the PivotTable list.
Move to Detail	Button	Moves selected field to Detail (data) area. Fields cannot be moved if either of the following is true: the creator of the PivotTable list restricted access to data area or the PivotTable list is based on source data from an OLAP database.
Promote	Button	Moves selected field to the next outer level (away from the data area).
Demote	Button	Moves selected field to the next inner level (closer to the data area).
Expand/Collapse	Toggle	Expands or collapses the data detail for selected field, item of data, or cell in data area. Unlike in Excel, there is only one button to toggle between Expand and Collapse. In Excel, these are separate buttons.
Refresh	Button	Updates data from source list or database. For more information, see the next section, "Using the Refresh Button."
Export to Excel	Button	Starts or switches to Excel and copies data into a new workbook as a PivotTable report. Should be used only when further analysis is needed. To place data in an Excel spreadsheet, use Copy.
Property Toolbox	Button	Displays the PivotTable Component's Property Toolbox. For more information about the Property Toolbox, see "The Property Toolbox" later in this article.
Field List	Button	Displays a hierarchical list of fields available from the PivotTable list's source data.
Help	Button	Displays Microsoft PivotTable List Help.

Using the Refresh Button

If the data in the PivotTable list can be refreshed, the Refresh button is available. However, there are some situations where the data cannot be refreshed:

• The PivotTable list cannot connect to the data source because of network problems, because the data source is on the other side of a firewall, or because of a lack of permissions.
• The data source no longer exists.
• The correct provider is not installed.
• Data from Web queries and imported text cannot be refreshed.

Using the Field List

The Field List (shown in Figure 11) contains all the fields in the data source that can be placed in the PivotTable Component. To place a field in a PivotTable list, simply drag a field from the Field List to the appropriate drop area (Row, Column, Filter, Data) within the PivotTable list.

Figure 11. The PivotTable Component Field List

The Property Toolbox

The Spreadsheet and PivotTable components use the Property Toolbox (shown in Figure 12) at design-time and run-time. The Chart Component uses the Property Toolbox only at design-time. The run-time version of the toolbox appears when the Property Toolbox button is clicked at run-time. The design-time toolbox is displayed when the Property Toolbox button is clicked at design-time. The design-time toolbox includes all the sections found in the run-time toolbox and additional sections specific to design and setup.

Figure 12. The run-time Spreadsheet Property Toolbox with General and Format expanded

The toolbox functions similar to a drop-down menu, except that multiple options can be expanded at the same time, as shown in Figure 12. Clicking the bar for a specific section expands that toolbox section. The sections displayed in the Property Toolbox change depending on what is selected within the component. For example, the Sort section is available when a row or column field is selected in the PivotTable Component, but not when a filter field or total is selected.

When a Web page contains more than one component, only one Property Toolbox is displayed. The settings adjust to reflect whatever component is active.

The Property Toolbox is not available at run time from a modal dialog box or form.

Working with the Components by Using Microsoft Visual Basic

The Office Web Components can also be used on VBA UserForms, Visual Basic forms, and Access forms. For the sake of simplicity, we will use the Visual Basic Editor available in any Office application.

To add Office Web Components to a UserForm in the Visual Basic Editor

1. Open the Visual Basic Editor (ALT+F11) from any Office application.
2. Insert a new UserForm.
3. Right-click the Toolbox.
4. On the shortcut menu, click Additional Controls.
5. In the Additional Controls dialog box, select the Microsoft Office Chart 9.0, Microsoft Office Data Source Control 9.0, Microsoft Office PivotTable 9.0, and Microsoft Office Spreadsheet 9.0 check boxes, and then click OK.
6. Drag the appropriate control from the Toolbox to the UserForm.

To add Office Web Components to a form in Microsoft Visual Basic 6.0

1. Right-click the Toolbox.

2. On the shortcut menu, click Components.

3. In the Components dialog box, select Microsoft Office Web Components 9.0 on the Controls tab.

   Six new controls will appear in the Toolbox. In addition to the Spreadsheet, Chart, PivotTable List, and Data Source controls, you will see a Record Navigation control and an Expand control. Because these two controls are used only by data access pages, we are not going to discuss them here.

When you insert controls in this manner, the components do not contain data by default. The components must be populated with data either through their controls or programmatically. The examples in the following sections show how to get data into the controls. These code examples are in the OWCExamples.xls workbook. Some of the sample code contains comments with more information, so be sure to review the code as well.

Working with the Spreadsheet Component

There are a couple of ways to get data into a Spreadsheet Component: by adding data at design time and by getting data at run time.

Adding Data at Design Time

The easiest way to add data is to type or paste values directly into the component. Anything placed in the cells at design time is displayed at run time; however, any changes made to the data in the spreadsheet at run time are not saved. The next time the component is displayed, the original data appears.

Another way to add data to the Spreadsheet Component requires specifying a URL to a file that contains the data you want to display; this file can either be a Web page that contains an HTML-formatted table or it can be a text file. The best way to create this file is to save an Excel file in the HTML file format because the data will have all the formulas and full-precision values encoded into the HTML.

Once you have created the file that contains the data you want to display, you can use the URL box in the Import Data section of the design-time Property Toolbox to point to the file, as shown in Figure 13. After you have entered the URL, you can retrieve the data from the file by clicking Import Now. If the URL points to a text file or an HTML table that is constantly being refreshed or built from a database, you will probably also want to select the Refresh from URL at run time check box in order to automatically get the most recent data. Note that the Import Data option can be used only to import data from a URL. It cannot be used to import data from ODBC data sources.

Figure 13. The Import Data section of the Spreadsheet Property Toolbox

When you import a Web page, data from the first table on the page is placed in the Spreadsheet Component; Web page formatting is also imported. Importing a text file simply imports raw data.

Getting Data at Run Time

Four properties of the Spreadsheet Component make it relatively easy to pass data to the control at run time: HTMLData, CSVData, HTMLUrl, and CSVUrl. The data properties allow you to pass an HTML or comma-delimited string to the spreadsheet. The URL properties allow you to pass the path to a Web page or comma-delimited text file. The following example appears behind the frmSS1 form in the OWCExamples.xls file:

Private Sub UserForm_Initialize()
    Dim strPath as String

    strPath = "file://c:\windows\win.ini"
    ' Fills the spreadsheet with the contents of the Win.ini file.
    Spreadsheet1.CSVURL = strPath
End Sub

Although you cannot pass an ActiveX Data Objects (ADO) recordset directly to the Spreadsheet Component, there is a method of the Recordset object that allows you to pass the recordset as a comma-delimited string to the CSVData property. The following example appears behind the frmSS2 form in the OWCExamples.xls file:

Private Sub UserForm_Initialize()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strPath as String
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    strPath = "C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb"
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPath
      
    rst.CursorType = adOpenKeyset
    rst.Open "Customers", cnn
    
    ' Replaces any existing data and places new data starting in A1.
    Spreadsheet1.CSVData = rst.GetString(adClipString, , ",")
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

There are some limitations to using the above method. For starters, using the CSVData property replaces all the data in the worksheet starting in cell A1. This behavior is okay if you are initializing the control, but it could rearrange your layout if the control is already visible. To place data in a specific location, use the ParseText method, as shown below:

Spreadsheet1.ActiveSheet.Range("B6").ParseText rst.GetString(), vbTab

Another limitation is that the GetString property of the Recordset object parses the data out by the delimiter you specify. When a field contains that character, that field gets parsed into two fields, or even to a separate line. For example, address fields that contain commas or carriage returns would be parsed into separate fields or lines. To work around this behavior, the OWCExamples.xls sample file contains a custom parsing function that strips out extra commas, line feeds, and carriage returns. To learn more, see the comment in the code behind the frmSS2 form in the sample file.

Another way to place data within the Spreadsheet Component is simply to assign values to cells through code. Although this process could be time-consuming if you need to put a lot of data into the spreadsheet, it may work well for simple spreadsheets. The following example creates a simple mortgage calculator and can be found behind the frmSS3 form in the OWCExamples.xls file:

Private Sub UserForm_Initialize()
    ' Places data in specific cells programmatically.
    ' This example creates a mortgage calculator in the form.
    
    With Spreadsheet1.ActiveSheet
        .Range("A1").Value = "Mortgage Calculator"
        .Range("A2").Value = "Principal"
        .Range("A3").Value = "Down Payment"
        .Range("A4").Value = "Term in Years"
        .Range("A5").Value = "Annual Rate"
        .Range("A6").Value = "Payment"
        .Range("B2").Value = 250000
        .Range("B3").Value = 50000
        .Range("B4").Value = 30
        .Range("B5").Value = 0.075
        .Range("B6").Formula = "=PMT(B5/12,B4*12,B2-B3)"
    
    ' AutoFit resizes columns.
        .Range("A:B").AutoFitColumns
    End With
End Sub

Working with the Chart Component

Once the Chart Component has been placed on a UserForm, there is no mechanism to put data into it at design-time. You can make minor modifications to the formatting of the Chart Component by right-clicking it and then clicking Property Toolbox on the shortcut menu. When a Chart Component is used on a UserForm, all of the data and most of the formatting has to be set programmatically.

There are several ways to get data into the Chart Component at run-time. All of them require using the SetData method to actually place the data into the Chart Component so we will look at this method in detail. The SetData method applies to the WCChart, WCErrorBars, and WCSeries objects and can be used to put data in any of these three objects.

The SetData method has three arguments: Dimension, DataSourceIndex, and DataReference. The Dimension argument refers to the piece of the chart being populated with data. The applicable dimension constants are SeriesNames, Categories, Values, YValues, XValues, OpenValues, CloseValues, HighValues, LowValues, BubbleValues, RValues, and ThetaValues. Each of these constants refers to a portion of the chart; for example, the SeriesNames constant refers to the name of each series, the OpenValues and CloseValues constants refer to the Open and Close prices for stock on a stock chart, and so on. Each of these constants is passed to the method as an enumerated constant in the form of chDimSeriesNames, chDimCategories, and so on.

The DataSourceIndex argument accepts two values: either chDataLiteral (which tells the control that the DataReference argument refers to literal values) or an index number referring to a data source in the chart's WCDataSources collection. Acceptable data sources include ADO recordsets, the Spreadsheet Component, the PivotTable Component or the Data Source control.

You send the actual data for the chart through the DataReference argument. When you use chDataLiteral for the DataSourceIndex argument, you can pass either a one-dimensional array or tab-delimited string to the DataReference argument. When you use a data source from the WCDataSources collection as the DataSourceIndex argument, you can pass in either an Excel-style range reference (for example, A1:A10) or a recordset field name. Passing a range reference to the SetData method is demonstrated in "Example 3: Using the Spreadsheet Component" later in this article.

The following three examples show how to use both literal data and a data source. The first example uses arrays to build a stock hi-lo chart. The second example pulls data directly from an ADO recordset. The third example uses the exact same ADO recordset, but it is used to populate a Spreadsheet Component that is used as the actual data source for the chart. These examples require two things: 1) that the Access Northwind Traders sample database, Northwind.mdb, be installed in the default location (c:\Program Files\Microsoft Office\Office\Samples), and 2) that a reference be set to the Microsoft ActiveX Data Objects 2.1 object library.

Example 1   Using Arrays

Note that each instance of the SetData method in this example uses the chDataLiteral constant because we are passing literal data in arrays. The following code can be found behind the frmChart1 form in the OWCExamples.xls file.

Private Sub UserForm_Initialize()
    Dim varCategories As Variant
    Dim varCloseValues As Variant
    Dim varLoValues As Variant
    Dim varHiValues As Variant
    Dim chtStock As OWC.WCChart
        
    varCategories = Array("1/3", "1/4", "1/5", "1/6", "1/7")
    varCloseValues = Array(116.5625, 112.625, 113.8125, 110, 111.4375)
    varLoValues = Array(112, 112.25, 109.375, 108.375, 107.4375)
    varHiValues = Array(118.625, 117.125, 116.375, 113.875, 112.25)
    
    ' Adds a chart title.
    With ChartSpace1
        .HasChartSpaceTitle = True
        .ChartSpaceTitle.Caption = "Microsoft Stock 1/3 - 1/7"
    End With
    
    ' Adds a chart to the ChartSpace object.
    Set chtStock = ChartSpace1.Charts.Add
    chtStock.Type = chChartTypeStockHLC
    
    ' Specifies which fields in the recordset are values and labels.
    chtStock.SetData chDimCategories, chDataLiteral, varCategories
    
    With chtStock.SeriesCollection(0)
        .SetData chDimCloseValues, chDataLiteral, varCloseValues
        .SetData chDimHighValues, chDataLiteral, varLoValues
        .SetData chDimLowValues, chDataLiteral, varHiValues
    End With
    
    ' Specifies scaling for axis.
    With chtStock.Axes(chAxisPositionLeft).Scaling
        .Maximum = 120
        .Minimum = 105
    End With
    
    ' Specifies gridlines.
    chtStock.Axes(chAxisPositionLeft).HasMinorGridlines = True
End Sub

Example 2   Using an ADO Recordset

This example requires only a Chart control on the UserForm. Because there is only one data source available to the chart, the SetData method uses 0 (zero) as the index number for the DataSourceIndex argument in both this and the following example. The code for this example can be found behind the frmChart2 form in the OWCExamples.xls file.

Private Sub UserForm_Initialize()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim chtTopTen As OWC.WCChart
    Dim strPath as String
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    strPath = "C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb"
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPath
    
    ' Creates the recordset.
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.Open "SELECT DISTINCTROW TOP 10 [Products].[ProductName] " & _
        "AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " & _
        "ORDER BY [Products].[UnitPrice] DESC;", cnn
    
    ' Connects the chart to the recordset and creates a title.
    With ChartSpace1
        .DataSource = rst
        .HasChartSpaceTitle = True
        .ChartSpaceTitle.Caption = "Ten Most Expensive Products"
    End With
    
    ' Adds a chart to the ChartSpace object.
    Set chtTopTen = ChartSpace1.Charts.Add
    
    ' Specifies which fields in the recordset are values and labels.
    With chtTopTen
        .HasLegend = True
        .SetData chDimCategories, 0, rst.Fields(0).Name
        .SetData chDimValues, 0, rst.Fields(1).Name
    End With
End Sub

Example 3   Using the Spreadsheet Component

This example requires that the UserForm contain both a Chart and a Spreadsheet control. The code for this example can be found behind the frmChart3 form in the OWCExamples.xls file.

Private Sub UserForm_Initialize()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim chtTopTen As OWC.WCChart
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    cnn.Open "NorthWind"
    
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.Open "SELECT DISTINCTROW TOP 10 [Products].[ProductName] " & _
        "AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " & _
        "ORDER BY [Products].[UnitPrice] DESC;", cnn
        
    ' Populates the spreadsheet with data from the recordset.
    Spreadsheet1.CSVData = rst.GetString(adClipString, , ",")
        
    ' Adds a chart title.
    With ChartSpace1
        .HasChartSpaceTitle = True
        .ChartSpaceTitle.Caption = "Ten Most Expensive Products"
    End With
            
    ' Assigns the chart's data source to the spreadsheet.
    ChartSpace1.DataSource = Spreadsheet1
    
    ' Adds a chart to the ChartSpace object.
    Set chtTopTen = ChartSpace1.Charts.Add
    
    ' Specifies which fields in the recordset are values and labels.
    With chtTopTen
        .HasLegend = True
        .SetData chDimCategories, 0, "A1:A10"
        .SetData chDimValues, 0, "B1:B10"
    End With
End Sub

Creating a Multiple Chart Layout

The Chart Component can host more than one chart. However, hosting multiple charts can be implemented only programmatically. The following example shows a UserForm that contains a Chart Component that hosts two charts. The Spreadsheet Component that is used as the data source contains data in cells A1:C5. The code creates a column chart and a doughnut chart in the same Chart Component. Both charts use column A for the category labels, but the first chart uses column B for data, and the second chart uses column C for data. The following code can be found behind the frmChart4 form in the OWCExamples.xls file.

Private Sub UserForm_Initialize()
    ChartSpace1.DataSource = Spreadsheet1
    
    ChartSpace1.Charts.Add 0
    ChartSpace1.Charts.Add 1
    
    With ChartSpace1.Charts(0)
        .Charts(0).SeriesCollection.Add
        .SeriesCollection(0).SetData chDimSeriesNames, 0, "B1"
        .SeriesCollection(0).SetData chDimCategories, 0, "A2:A5"
        .SeriesCollection(0).SetData chDimValues, 0, "B2:B5"
        .HasTitle = True
        .Type = chChartTypeColumnClustered
    End With
    
    With ChartSpace1.Charts(1)
        .SeriesCollection.Add
        .SeriesCollection(0).SetData chDimSeriesNames, 0, "C1"
        .SeriesCollection(0).SetData chDimCategories, 0, "A2:A5"
        .SeriesCollection(0).SetData chDimValues, 0, "C2:C5"
        .HasLegend = True
        .HasTitle = True
        .Type = chChartTypeDoughnut
    End With
End Sub

You can control the layout of multiple charts by using the four properties listed in the following table. The first two properties can be set in the Properties window; the last two have to be set through code.

Property	Applies to	Description
ChartLayout	ChartSpace object	Controls whether multiple charts are displayed in rows or columns. Accepts values of: 0   chChartLayoutAutomatic 1   chChartLayoutHorizontal 2   chChartLayoutVertical
ChartWrapCount	ChartSpace object	Controls number of charts on a row or column (as determined by the ChartLayout property).
HeightRatio	WCChart object	Value defaults to 100. If there are two default charts, the height would be 50% of the total ChartSpace object (half of 200). If there are three default charts, the height would be 33%. Actual height percentage for a single chart follows the formula: HeightRatio/(the sum of the maximum HeightRatio for each row).
WidthRatio	WCChart object	Behaves the same as HeightRatio, except it controls the width of the charts.

If there are multiple charts in both rows and columns, each row or column expands to the largest ratio of any chart contained in that row or column. In Figure 14, the HeightRatio property for the chart in the upper-left corner was set to 200. The WidthRatio property for the chart in the bottom-right corner was also set to 200. The rest of the ratio settings were left at the default. The Chart Component itself was 600x400, so with these settings, the first row takes up 66% of the row space and the third column takes 50% of the column space.

Figure 14. Multiple charts hosted in the same Chart Component

Working with the PivotTable Component

You can easily put data into the PivotTable Component at design time, but it is also relatively easy to put data into it at run time.

Getting Data at Design-Time

The PivotTable Component is unique in that there is an interface to connect it to existing data at design time. In the Data Source section of the Property Toolbox, you can create a connection to any OLE DB data source. To create the connection, click the Connection Editor button to step through a wizard that builds a connection string to the data source.

Note   Although the option is available through the user interface, there is no way to set the data source at design-time to a Data Source control—not even on a VBA UserForm containing a Data Source control. However, the PivotTable component's DataSource property can be set to a Data Source control at run time.

Once you have gone through the wizard and built a connection to a data source, the Data Members list in the Data Source section of the Property Toolbox (shown in Figure 15) is populated with elements from the database to which the PivotTable Component is connected. When you select one of these items, the Field List is automatically populated with whatever fields are returned by that item. To use an SQL statement or a query as the data source, you must specify the SQL statement or query in the Command Text box.

Figure 15. The Data Source section of the PivotTable Property Toolbox

Once a connection is created and either a data member is selected or command text is entered, you can drag fields from the Field List to the drop areas within the PivotTable Component. When the form containing the PivotTable Component is run, the PivotTable list will reflect whatever fields you placed in it during design-time. Users can move fields around or delete them, but theses changes will not be saved after the form is closed.

Getting Data at Run Time

The same information that is required at design-time to connect to data has to be specified at run-time through code. The following examples show how to connect the PivotTable List control to a database by using the ConnectionString property, how to select which fields appear in the PivotTable list, and how to use an XML file as the data source.

Example 1   Connecting Directly to a Database

This example connects to the Northwind Traders sample database and uses the AutoLayout method to populate the data area of the PivotTable list with all the fields in the Customers table. This code can be found behind the frmPT1 form in the OWCExamples.xls sample file.

Private Sub UserForm_Initialize()
    Dim strPath as String

    strPath = "c:\program files\microsoft office\office\samples\northwind.mdb"

    ' Creates connection to Northwind and Customers table.
    PivotTable1.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & _
        "data source=" & strPath
    PivotTable1.DataMember = "Customers"
    
    ' Automatically populates the PivotTable Component.
    PivotTable1.ActiveView.AutoLayout
End Sub

Example 2   Specifying Where the Fields Are Placed

This example builds on the preceding example. It uses the same connection, but rather than automatically populate the PivotTable list, we use the InsertFieldSet method to place data in the various axes (filter, row, data). The fields in the data area are accessed through the FieldSets property. The FilterMember property is used to set a default filter on the Country field. The code for this example can be found behind the frmPT2 form in the OWCExamples.xls sample file.

Private Sub UserForm_Initialize()
    Dim strPath as String

    strPath = "c:\program files\microsoft office\office\samples\northwind.mdb"

    ' Creates connection to Northwind and Customers table.
    PivotTable1.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & _
        "data source=" & strPath
    PivotTable1.DataMember = "Customers"
    
    With PivotTable1.ActiveView
        .FilterAxis.InsertFieldSet .FieldSets("Country")
        .RowAxis.InsertFieldSet .FieldSets("Region")
        .DataAxis.InsertFieldSet .FieldSets("CompanyName")
        .DataAxis.InsertFieldSet .FieldSets("ContactName")
        .DataAxis.InsertFieldSet .FieldSets("Phone")
        .FieldSets("Country").FilterMember = "USA"
    End With
End Sub

Example 3   Using an XML File as a Data Source

A PivotTable Component cannot be directly connected to an ADO recordset. However, an ADO recordset can be saved as an XML file, and the PivotTable Component can read an XML stream. The following code sample (behind the frmPT3 form in the sample file) saves the ADO recordset and then connects the PivotTable Component to the file that has been saved:

Sub UserForm_Initialize()
    ' Creates the recordset and saves it to an XML file.

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strPath As String
    Dim strNewLocation

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    strPath = "C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb"
    strNewLocation = "c:\customerlist.xml"

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strPath
        
    ' Creates the recordset and saves it to an XML file.
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .Open "Customers", cnn
        .Save strNewLocation, adPersistXML
    End With
    
    ' Connects the PivotTable Component to the XML file and
    ' automatically populates it with data.
    With PivotTable1
        .ConnectionString = "Provider=MSPersist"
        .CommandText = strNewLocation
        .ActiveView.AutoLayout
    End With
End Sub

Working with the Components in Microsoft FrontPage

With Microsoft FrontPage, you can both edit pages containing the Office Web Components as well as create new pages that use the Office Web Component controls.

Creating a New Page

Inserting the Office Web Components into a new page is very simple. If the Office Web Components have been installed on the computer, point to Component on the Insert menu in FrontPage, and then click the component you want to insert. The component will be inserted wherever the insertion point is currently located.

When you insert an Office Web Component into a Web page, FrontPage not only inserts the basic <object> tag to implement the selected component, but also a set of default parameters and tags. To view this code, click the HTML tab in FrontPage.

To set up the Spreadsheet and PivotTable components in FrontPage, you select them and manipulate their controls just as you would in Visual Basic or Access. However, when you insert a Chart Component into the document, you are automatically prompted to set up the component with the Microsoft Office Chart Wizard if data sources are available in the Web page. If no data sources are available, you receive an explanatory message and the Chart Wizard does not appear. (Note that the Chart Wizard is not available in the Visual Basic Editor.)

The Microsoft Office Chart Wizard (shown in Figure 16) is very similar to the Chart Wizard in Microsoft Excel. However, there are some differences in the chart types available because, unlike Excel, the Chart Component supports polar charts but not 3-D charts.

Figure 16. Step 1 of the Microsoft Office Chart Wizard

When a Chart Component has been added to a Web page through the Chart Wizard, FrontPage also adds a Data Source control to the page to bind the Chart control to whatever data source was used. Removing the Data Source control from the page will result in a blank chart.

Modifying an Existing Page

To modify a published page that contains Office Web Components, simply open it within FrontPage. Although data access pages created in Microsoft Access can be edited in FrontPage, it is recommended that Access be used to edit them. Once the page is open, the Office Web Component's structure, as well as its supporting code, can be modified. To modify a component through its toolbar and the Property Toolbox, first click the Normal tab in FrontPage, and then click the control once to put it in edit mode.

To modify the PivotTable Component page

1. In FrontPage, open the OWCPT.htm file that we created in the "Publishing Examples" section earlier in this article. Click the Normal tab.
2. In this PivotTable list, each city has two rows showing the Average Income and Average Home Price. To consolidate these rows, click the title bar (it should contain something like "PivotTable3") to select the entire PivotTable list, and then click Property Toolbox on the toolbar.
3. In the Property Toolbox, click the Totals section. In the Total orientation box, select Column. The data will now appear two columns.
4. With the entire PivotTable list still selected, click the Show/Hide section and then the Drop areas button to remove the "Drop Column Fields Here" text from the PivotTable list.
5. Click the Format section and give the PivotTable list this title: Average Income and Home Prices.
6. To change the titles of the columns, click the Sum of Average Home Price cell, and then click the Total Caption section in the Property Toolbox. Remove the "Sum of" text. Do the same for the Sum of Average Income cell.
7. To remove the expand indicators from each row, click the City cell and then click the Show/Hide section in the Property Toolbox. Click the Expand indicators button to remove the plus (+) sign beside each row.

The Code Behind the Components

A combination of HTML and XML code is used to create the Office Web Components on a Web page. This section discusses what the code behind the components looks like.

The <Object> Tag

The <object> tag identifies what Office Web Component is being used by the Web page, the ClassID for the component, and the codebase attribute (for more information about this attribute, see the Publishing Without the Components Installed section earlier in this article), as shown in the following examples.

The <object> tag published for the Spreadsheet Component:

<object id="SpreadSheet1" classid="CLSID:0002E510-0000-0000-C000-000000000046">

The <object> tag published for the Chart Component:

<object id="Chart1" classid="CLSID:0002E500-0000-0000-C000-000000000046" width="80%" height="80%">

The <object> tag published for the PivotTable Component:

<object id="PivotTable1" classid="CLSID:0002E520-0000-0000-C000-000000000046">

The <object> tag published for the Data Source control:

<object id="msodsc" classid="CLSID:0002E530-0000-0000-C000-000000000046">

The <Param> Tag

Within the opening and closing sets of <object> tags, there are sets of <param> tags. These tags contain formatting information that specifies how the component is displayed in Internet Explorer. When a component has been published from Microsoft Excel, the last <param> tag contains the actual data that was exported from within Excel. The data is identified in the <param> tag as either HTML or XML by the name attribute.

<param name=XMLData value=...>
<param name=HTMLData value=...>

Because the data in the value attribute of the <param> tag is encoded, it is not easily readable, as shown by the following HTMLData code created by FrontPage:

<x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>

The HTML or XML encoding uses the ISO Latin-1 character set with either the HTML named entity reference or the decimal code for some of the characters. The data can be deciphered if you use the following table, which contains several of the more common encoded characters.

Encoded text	Actual text	Encoding type
&lt;	<	Named entity
&gt;	>	Named entity
&#9;	Tab	Decimal code
&#10;	Linefeed	Decimal code
&#13;	Carriage return	Decimal code
&quot;	" (double quotation marks)	Named entity
&amp;	&	Named entity

The code shown above would actually read:

<x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>

**Note   **A table containing the full ISO Latin-1 character set can be found on page 1331 of the Dynamic HTML Reference and Software Development Kit (Microsoft Press, 1999).

Modifying Code in FrontPage

Once you understand the underlying HTML behind the components, it is relatively easy to modify a page containing an Office Web Component. When the component is selected in Normal view, the corresponding HTML code is selected in HTML view so you can view the code behind a specific component. You can then make changes to specific properties or sections without using the component user interface. As noted previously, some of the HTML code is encoded so it can be very difficult to edit. Note that once encoded HTML code has been modified, Microsoft no longer supports it. Microsoft recommends that changes be made to the Web components only through their respective user interfaces, not by modifying the code behind them; therefore you make changes to code at your own risk.

Scripting in FrontPage

Adding script behind the components is easy in FrontPage as well. The components can be scripted with either VBScript or Microsoft® Jscript®, but for the sake of this discussion, we will use only VBScript examples. The best way to become familiar with the components and what can be done with them through script is to view their object models and become familiar with the objects, methods, and properties of each.

To view the one of the components in the Object Browser

1. Open the OWCExamples.xls sample file.
2. Press ALT+F11 to open the Visual Basic Editor.
3. Click the Object Browser button on the toolbar.
4. In the drop-down list in the upper left corner of the Object Browser, click OWC.

The Classes pane on the bottom left shows all the objects and constants. The Members pane on the bottom right shows either the methods, properties, and events associated with the object that is selected in the left pane or the constants that are members of the constant selected in the left pane.

For graphical representations of the Office Web Component object models, see the Msowcvba.chm Help file or see Microsoft Office Web Components Object Models.

Because the examples shown earlier in the Working with the Components by Using Visual Basic section can also be used in VBScript, I am not going to repeat them all here, but will only show one example for each component to show what syntax to use.

When you are making the change from Visual Basic or VBA code to VBScript code, you need to be aware of the following:

• Any script added to a Web page must be enclosed in opening and closing <script> tags that appear within the opening and closing <html> tags.
• Script should also be surrounded by HTML comment tags (<!-- and -->) so that older browsers will ignore the script.
• All variables are variants.
• You can't create references to type libraries; therefore, all objects must be created in code by using the CreateObject function. For example:

Set cnn = CreateObject("ADODB.Connection")

• You can use the Option Explicit statement, but you have to add it yourself.
• VBScript does not support the use of named constants. When the Internet Explorer scripting engine encounters them, the constants are treated as uninitialized variables with values of 0. To use the named constants for the Office Web Components, you must create a reference by using the Constants property as shown below and then preface the constants with that object. When you are using constants from other libraries, such as ADO, make sure you use the numeric value that corresponds to the constant.

Set c = ChartSpace1.Constants
Set cht = ChartSpace1.Charts.Add()
cht.Type = c.chChartTypeBubble

Since we are not loading the components through a form, we of course do not need to use "Sub UserForm_Initialize()" in the VBScript examples. For these examples, we will run the code from the Load event of the Window object. To use any of the VBA examples, enclose the code in <script> tags and place it within the <html> tags in a Web page that contains the appropriate component.

Spreadsheet Example

<script language="VBScript">
<!--
Sub Window_Onload()
    strPath = "file://c:\windows\win.ini"
    Spreadsheet1.CSVURL = strPath
End Sub
-->
</script>

Chart Example

<script language="VBScript">
<!--
Sub Window_Onload()
    strPath = "c:\program files\microsoft office\office\samples\northwind.mdb"

    ' Creates the recordset.
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "provider=microsoft.jet.oledb.4.0;" & _
        "data source=" & strPath 
    Set rst = CreateObject("ADODB.Recordset")

    ' Note use of numeric values instead of constants.
    rst.CursorLocation = 3
    rst.CursorType = 1

    rst.Open "SELECT DISTINCTROW TOP 10 [Products].[ProductName] AS " & _
        "TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY " & _
        "[Products].[UnitPrice] DESC;", cnn

    ' Connects the chart to the recordset and creates a title.
    With ChartSpace1
        .DataSource = rst
        .HasChartSpaceTitle = True
        .ChartSpaceTitle.Caption = "Ten Most Expensive Products"
    End With

    Set c = ChartSpace1.Constants

    ' Adds a chart to the ChartSpace object.
    Set chtTopTen = ChartSpace1.Charts.Add

    ' Specifies which fields in the recordset are values and labels.
    chtTopTen.HasLegend = True
    chtTopTen.SetData c.chDimCategories, 0, rst.Fields(0).Name
    chtTopTen.SetData c.chDimValues, 0, rst.Fields(1).Name
End Sub
-->
</script>

PivotTable Example

<script language="VBScript">
<!--
Sub Window_Onload()
    strPath = "c:\program files\microsoft office\office\samples\northwind.mdb"

    ' Creates connection to Northwind and Customers table.
    PivotTable1.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & _
        "data source=" & strPath    
    
    PivotTable1.DataMember = "Customers"

    ' Automatically populates the PivotTable.
    PivotTable1.ActiveView.AutoLayout
End Sub
-->
</script>

Using Microsoft Office Web Components in Data Access Pages

This section discusses using Office Web Components within the Access environment. The components are best suited for use in data access pages. Although the Office Web Components can be placed on Access forms, they cannot be modified at all at design time; when the components are placed on Access forms, you must use code to modify them or pass data to them.

To place any of the components in a data access page, simply click the appropriate button in the Toolbox and place the cursor on the page where you want it to appear and click once.

Working with the Spreadsheet Component

Once a Spreadsheet Component has been placed on a data access page, it can pull values from other controls on the data access page if you enter the following formula in the cells, where controlname is the name of a control on the data page:

=document.controlname.value

For example, if the data access page contained a control named Text1, the following formula would return whatever value was in that control:

=document.Text1.value

Working with the Chart Component

When a Chart Component is added to a data access page within an existing database, the Chart Wizard appears to guide you through creating the chart. If the data access page contains a PivotTable or Spreadsheet component, you can choose to use the component as the data source instead of using the database, as shown in Figure 17.

Figure 17. Step 2 of the Microsoft Office Chart Wizard

Working with the PivotTable Component

There are two ways to put a PivotTable Component into a data access page:

• Click the Office PivotTable tool in the data access page toolbox and then click on the data access page where you want the upper-left corner of the PivotTable List control to be. Like Microsoft Excel, Access uses the Data Source control to connect the PivotTable to its data source. When you place the PivotTable List control on a data access page and then go to the Data Source section of the Property Toolbox, the Data source control option is already selected, and tables in the database appear in the Data member list, as shown in Figure 18. The Data Source control being used is hidden and cannot be modified.

  

  Figure 18. The Data Source section of the PivotTable Property Toolbox

• You can also add a PivotTable Component to a page by displaying the Field List and then dragging a table or query onto the page. You are then prompted by the Layout Wizard to select whether you want to insert the table or query as individual controls or as a PivotTable list, as shown in Figure 19.

  

  Figure 19. Data Access Page Layout Wizard

  When you click PivotTable List, Access creates a PivotTable list with no visible controls and all of the fields displayed in the data area. To move the fields to different areas or to view the PivotTable Component toolbar, right-click the PivotTable list and then click Property Toolbox on the shortcut menu. You can use the Show/Hide section of the Property Toolbox to display the drop areas, the toolbar, and other elements of the PivotTable list.

Conclusion

In this article, we have discussed publishing the Microsoft Office Web Components from Microsoft Excel and working with them in several environments: Internet Explorer, the Visual Basic Editor, Microsoft FrontPage, and Microsoft Access. The components can very useful for adding data-analysis features to your Web solutions. This article should give you a good starting point for adding the components to your projects. If you want to pursue a deeper understanding of how the components work and what can be done with them, there are many other resources available. The best overall resource available on the Office Web Components is the first reference listed below.