Try using the following code in an html file:
** Begin of code
****************************************************************
<html>
<head>
<meta http-equiv=Content-Type content="text/html charset=windows-1252">
<meta name=ProgId content=FrontPage.Editor.Document>
</head>
<script language="vbscript">
Sub Window_OnLoad()
' colors constants
cRed = 255
cGreen = 65280
cBlue = 16711680
cBlack = 0
cWhite = cRed+cGreen+cBlue
cYellow = cRed+cGreen
cMagenta = cRed+cBlue
cCyan = cGreen+cBlue
Dim oPT
Dim cnnConnection
Dim c
Dim view
Dim tblFrame
Dim trTemp
Dim tdTemp
self.document.title = "Pivot Table Test"
Set tblFrame = self.document.createElement("TABLE")
tblFrame.cellPadding = 0
tblFrame.cellSpacing = 0
tblFrame.style.height = "100%"
tblFrame.style.width = "100%"
Set trTemp = tblFrame.insertRow()
Set tdTemp = trTemp.insertCell()
tdTemp.style.height = "100%"
tdTemp.style.width = "100%"
Set oPT = self.document.createElement("OBJECT")
oPT.classid = "CLSID:0002E55A-0000-0000-C000-000000000046"
oPT.width = "100%"
oPT.height = "100%"
Dim strProvider
strProvider = "Microsoft.Jet.OLEDB.4.0"
' Create an ADO object
Set cnnConnection = CreateObject("ADODB.Connection")
' Set the provider and open the connection to the database
cnnConnection.Provider = strProvider
cnnConnection.Open "c:\data\pivottest.mdb"
' Set the pivot table's connection string to the cnnConnection's
connection string
oPT.ConnectionString = cnnConnection.ConnectionString
' SQL statement to get everything from table1
oPT.CommandText = "select * from table1"
' Get variables from the pivot table
Set view = oPT.ActiveView
Set c = oPT.Constants
view.AutoLayout
' Add Category to the Row axis and Item to the Column axis
view.RowAxis.InsertFieldSet(view.FieldSets("Category"))
view.FieldSets("Category").AlwaysIncludeInCube = true
view.ColumnAxis.InsertFieldSet(view.FieldSets("Item"))
view.FieldSets("Item").AlwaysIncludeInCube = true
view.AddTotal "PriceSum", view.FieldSets("Price").Fields(0),
c.plFunctionSum
Dim rowValue
Dim colValue
rowValue = "Food"
colValue = "Pants"
Dim newtotal
Set newtotal = view.AddCalculatedTotal("Price", "Price",
"'PriceSum', back_color = 'iif([Category].CurrentMember.Name =
"""&rowValue&""" and [Item].CurrentMember.Name = """&colValue&""",
"&cYellow&", "&cWhite&")', fore_color = '"&cBlack&"'")
view.DataAxis.InsertTotal newtotal
view.DisplayCellColor = true
' Set some visual properties
oPT.DisplayExpandIndicator = False
oPT.DisplayFieldList = False
oPT.AllowDetails = False
oPT.AutoFit = False
tdTemp.appendChild(oPT)
with (self.document.body)
.leftMargin = 0
.rightMargin = 0
.topMargin = 0
.bottomMargin = 0
.style.border = "0px solid"
.appendChild(tblFrame)
End With
' MsgBox oPT.XMLData
End Sub
</script>
<body></body>
</html>
** End of code
****************************************************************
Create pivottest.mdb database as indicate in the following link
http://support.microsoft.com/kb/235542
Regards!
Ace73
