[Previous] [Contents]

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, there are still many companies using Excel 97. If you need your code to work on a legacy platform, you should be aware of how pivot tables were created in Excel 97.

In Excel 97, you would use the PivotTableWizard method. Take a look at the code for building a simple pivot table showing revenue by region and line of business. Where current code uses two steps (add a PivotCache and then use CreatePivotTable), Excel 97 would use 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(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.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:="R2C13", TableName:="PivotTable1")

    PT.ManualUpdate = True
    ' Set up the row fields
    PT.AddFields RowFields:="Region", ColumnFields:="Line of Business"

    ' 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

[Previous] [Contents]