Understanding Special Considerations for Excel 97
Pivot tables and VBA took a radical turn in Excel 2000. In Excel 2000, Microsoft introduced
the
PivotCache object. This object allows you to define one pivot cache and then build many
pivot reports from the pivot cache.
Officially, Microsoft quit supporting Excel 97 a few years ago. But, in practical terms, many
companies are still using Excel 97. If you need your code to work on a legacy platform, you
should be aware of the way pivot tables were created in Excel 97.
In Excel 97, you used the
PivotTableWizard method. Take a look at the code for building a
simple pivot table showing revenue by region and product. Where current code uses two
steps (add
PivotCache and then use CreatePivotTable), Excel 97 used just one step, using
the
PivotTableWizard method to create the table:
Sub PivotExcel97Compatible()
‘ Pivot Table Code for Excel 97 Users
Dim WSD As Worksheet
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
‘ Define input area
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
‘ Create pivot table using PivotTableWizard
Set PT = WSD.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=PRange.Address, _
TableDestination:=”R2C18”, TableName:=”PivotTable1”)
PT.ManualUpdate = True
‘ Set up the row fields
PT.AddFields RowFields:=”Region”, ColumnFields:=”Product”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0,K”
.Name = “Total Revenue”
End With
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
Chapter 11 Using VBA to Create Pivot Tables
288
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 288
To Next Page
Loading...
Need help?
General
