create pivot table code not working in Access 2002

C

Chris_n_Mihaela

Created macro in Excel, moved to access, worked fine in
Office 97, but crashes in Access 2002.

Original Excel Code:
..ActiveWorkbook.PivotCaches.Add(SourceType.xlDatabase,
SourceData:= _
"'Comparison on Local'!
R1C1:R15000C50").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1",
DefaultVersion:= _
xlPivotTableVersion10

Modified Access Code:
..Application.ActiveWorkbook.PivotCaches.Add
(SourceType:=.Application.xlDatabase, SourceData:= _
"'Comparison on Local'!
R1C1:R15000C50").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1",
DefaultVersion:= _
xlPivotTableVersion10

I think the problem is that my "Activeworkbook" object
doesn't have the PivotCaches object.

Any suggestions on how to:

1) preferrably just edit excel VB and paste into Access
2002 so it creates pivot table automatically?

2) alternatively, custom code to create pivot table and/or
pivot cache in an existing worsheet?

Thanks!
 
Top