PivotCache Object

[Quintios]

I am attempting to write the VBA to create a pivot table in Excel. I found a nifty bit of code, but it was written for Excel 2000 and according to the author's website:

The PivotCache object does not exist in Excel 97. Consequently, most of the examples require Excel 2000. These macros can, however, be rewritten for Excel 97.

Indeed, the macro craps out. What do I need to do to replace the PivotCache object so I can use the macro in Excel 97?

Here's the code:

Sub CreatePivotTableFromDB()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
'   Delete PivotSheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("PivotSheet").Delete
    On Error GoTo 0
    
'   Create a Pivot Cache
' This is the code that is not compatible with
' Excel 97
    Set PTCache = ActiveWorkbook.PivotCaches.Add _
      (SourceType:=xlExternal)
    
'   Path to database file
    DBFile = ThisWorkbook.Path & "\budget.mdb"
    
'   Connection String
    ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

'   QueryString
    QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
      "\BUDGET`.Budget Budget"
    
    With PTCache
        .Connection = ConString
        .CommandText = QueryString
    End With
    
'   Add new worksheet
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"

'   Create pivot table
    Set PT = PTCache.CreatePivotTable( _
      TableDestination:=Sheets("PivotSheet").Range("A1"), _
      TableName:="BudgetPivot")
    
'   Add fields
    With PT
'       Add fields
        .PivotFields("DEPARTMENT").Orientation = xlRowField
        .PivotFields("MONTH").Orientation = xlColumnField
        .PivotFields("DIVISION").Orientation = xlPageField
        .PivotFields("BUDGET").Orientation = xlDataField
        .PivotFields("ACTUAL").Orientation = xlDataField
    End With
End Sub

Onwards,

Q-

 

[xlbo]

This is NOT a complete answer but just an example of how a pivottable may be created in code in xl97:
(watch out for line wrap)
Set pglSht = .Sheets(.Sheets.Count)
With pglSht
lRow = .Range("A65536&quot.End(xlUp).row
.PivotTableWizard SourceType:=xlDatabase, SourceData:=.Range("A1:H" & lRow), TableDestination:=.Range("K1&quot, TableName:="PvtPGL"
.PivotTables("pvtPGL&quot.AddFields RowFields:=Array("Region", "Lease&quot
With .PivotTables("pvtPGL&quot.PivotFields("ID&quot
.Orientation = xlDataField
.Name = "Houses"
.Function = xlCount
End With
end with Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur