Extending the Data Analysis Features in the Microsoft Office Web Components | Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Typically, data analysis solutions that automate the Microsoft Office Web Components use code associated with Web pages. In the following example, available in the SalesPivotCode.htm file in the Chap11 folder, the Web page uses VBScript to create, customize, and display instances of the Spreadsheet Component, PivotTable Component, and Chart Component. The components connect to data in the BookSale.mdb file (also included in the Chap11 folder).

Note

For brevity, only a code sample demonstrating the Office XP Web Components is featured in this section. For code samples demonstrating the Office 2000 Web Components, see the Office 2000 Web Components online help or the Microsoft Developer Network (MSDN) at http://msdn.microsoft.com.

<html> <head> <title>Office Web Components Code Examples</title> </head> <body> <p>An invisible Microsoft Office Data Source Control connects to an Access database in the background.</p> <object id=MSODSC classid=CLSID:0002E553-0000-0000-C000-000000000046 VIEWASTEXT></object> <p>Spreadsheet Component Code Example</p> <object id=Spreadsheet1 classid=CLSID:0002E551-0000-0000-C000-000000000046 VIEWASTEXT></object> <p>PivotTable Component Code Example</p> <object id=PivotTable1 classid=clsid:0002E552-0000-0000-C000-000000000046 VIEWASTEXT></object> <p>Chart Component Code Example</p> <object id=ChartSpace1 classid=CLSID:0002E556-0000-0000-C000-000000000046 VIEWASTEXT></object> <script id=clientEventHandlersVBS language=vbscript> <!-- Sub window_onload Dim objPivotView Dim objTotal Dim objConstants Dim objChart ’ Connect to the BookSale.mdb database. ’ Change the ConnectionString property to match ’ wherever you store the Booksale.mdb file on your computer. MSODSC.ConnectionString =  "Provider=Microsoft.Jet.OLEDB.4.0;Password=""""; User ID=Admin;Data Source=C:\Microsoft Press\Excel Data Analysis\Sample Files\Chap11\BookSale.mdb;Mode=S hare Deny None;Extended Properties=""""; Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""; Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5; Jet OLEDB:Database Locking Mode=1; Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Global Bulk Transactions=1; Jet OLEDB:New Database Password=""""; Jet OLEDB:Create System Database=False; Jet OLEDB:Encrypt Database=False; Jet OLEDB:Don’t Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False; Jet OLEDB:SFP=False" ’ Connect the Spreadsheet Component to the Book Store Sales table in ’ the Microsoft Office Data Source Control’s referenced database. Spreadsheet1.DataSource = MSODSC Spreadsheet1.DataMember = "Book Store Sales" ’ Connect the PivotTable Component to the Book Store Sales table in the ’ Microsoft Office Data Source Control’s referenced database. PivotTable1.DataSource = MSODSC PivotTable1.DataMember = "Book Store Sales" Set objPivotView = PivotTable1.ActiveView Set objConstants = PivotTable1.Constants ’ Create the Sum of Sales field for the PivotTable Component. Set objTotal = objPivotView.AddTotal("Sum of Sales", _ objPivotView.FieldSets("Sales").Fields(0), _ objConstants.plFunctionSum) ’ Add the Store Name, Quarter, and Sales fields to the row, page, and ’ data areas of the PivotTable Component. objPivotView.RowAxis.InsertFieldSet objPivotView.FieldSets("Store Name") objPivotView.FilterAxis.InsertFieldSet objPivotView.FieldSets("Quarter") objPivotView.DataAxis.InsertFieldSet objPivotView.FieldSets("Sales") objPivotView.DataAxis.InsertTotal objTotal ’ Connect the Chart Component to the PivotTable Component ’ as its data source. ChartSpace1.DataSource = PivotTable1 ’ Change the Chart Component’s view to a three-dimensional ’ clustered column type. Set objChart = ChartSpace1.Charts.Item(0) objChart.Type = objConstants.chChartTypeColumnClustered3D End Sub --> </script> </body> </html>

Let’s review this code in more detail. To connect to the data, an instance of the Microsoft Office Data Source Control (MSODSC) is used. While the MSODSC has no visible representation on the Web page, it makes managing data source connections much easier when using code. The Office XP version of the MSODSC is identified by the class ID 0002E553-0000-0000-C000- 000000000046. (The computer uses this class ID along with the computer’s registry to determine which specific ActiveX control to use.) To refer to the MSODSC in code, the MSODSC is given the friendly ID of MSODSC; the friendly ID is a more convenient way to refer to the ActiveX control in code than using the hard-to-remember class ID. The Spreadsheet component on the Web page is identified by the registry using the class ID 0002E551-0000-0000-C000- 000000000046 and the friendly ID Spreadsheet1 for coding purposes. Similarly, the PivotTable Component on the Web page is identified by the class ID 0002E552-0000-0000-C000-000000000046and the friendly ID PivotTable1, and the Chart Component on the Web page is identified by the class ID 0002E556-0000-0000-C000-000000000046 and the friendly ID ChartSpace1.

The <script>tag is used to define code that will run when the Web page is displayed or when a user or a particular Web page component takes some sort of action. The language attribute is set to vbscript.

In this example, only one event subroutine, window_onload, will run when the Web page is initially displayed. In the window_onload event subroutine, objects are declared representing the PivotTable component (objPivotView), a PivotTable data field (objTotal), Office XP Web Components constants (objConstants), and a chart (objChart).

Note

In VBScript, all object instances must be declared as the Variant data type. For example, in VBScript, the following code will cause an error:

Dim objChart As Excel.Chart

Note

In most of the Office object models, you can use constants in code without declaring them first. In the Office Web Components object models and VBScript, you must declare constants before you use them in code.

Next the MSODSC object’s ConnectionString property is set to reference the BookSale.mdb file in a folder named C:\Microsoft Press\Excel Data Analysis\Sample Files\Chap11. You should change this code in the SalesPivotCode.htm file to match the folder where you have your copy of BookSale.mdb.

Once the MSODSC connects to the BookSale.mdb file, the DataSource and DataMember properties of the Spreadsheet Component and PivotTable Component objects are set to the MSODSC object’s data source (the BookSale.mdb file) and, specifically, the Book Store Sales table in the BookSale.mdb file. Later in the code, after the PivotTable view has been built, the Chart Component object’s DataSource property is set to the PivotChart Component object to synchronize the Chart Component with the PivotTable Component.

The rest of the code that refers to the PivotTable Component object (objPivotView) is similar to the code in the CreatePivotTableAndPivotChartReport subroutine presented in the Excel code samples section earlier in this chapter:

The PivotTable Component object’s ActiveView property refers to what is currently visible in the PivotTable Component on the Web page, which is known programmatically as a PivotView object. The PivotView object’s AddTotal method creates a total field, and the PivotDataAxis object’s InsertTotal method adds the total field to the PivotTable Component’s data area. The basis of the total field, named Sum of Sales, consists of the Sales field’s summary data.
To add fields to the PivotTable Component’s row, page, and data areas, the PivotView object’s RowAxis, FilterAxis, and DataAxis objects are first referenced. The InsertFieldSet method of each object references the corresponding field (the Store, Quarter, and Sales fields in this instance) to add the fields.

The results of running the code in the SalesPivotCode.htm file are shown in Figures 11-16, 11-17, and 11-18.

click to expand
Figure 11-16: The SalesPivotCode.htm file’s Spreadsheet Component.

click to expand
Figure 11-17: The SalesPivotCode.htm file’s PivotTable Component.

click to expand
Figure 11-18: The SalesPivotCode.htm file’s Chart Component.