Creating an older version pivottable

[IgorM]

Hi

How to programmatically create an older version of pivottable in Excel 2007?

Kind regards
IgorM

 

[Roger Govier]

Hi Igor

in XL2007 the code would look something like
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R9C3", Version:=xlPivotTableVersion12).CreatePivotTable
_
TableDestination:="", TableName:="PivotTable3", DefaultVersion:= _
xlPivotTableVersion12
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Value"), "Sum of Value", xlSum

For XL2003 the code would be
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet2!R1C1:R9C3").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Value"), "Sum of Value", xlSum
End Sub

So the difference is that when you create your code in XL2007 (using macro
recorder for example), then just edit the generated code to change
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase
to
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase

and change
DefaultVersion:= _
xlPivotTableVersion12
to
DefaultVersion:= _
xlPivotTableVersion10

--
Regards
Roger Govier

Hi

How to programmatically create an older version of pivottable in Excel
2007?

Kind regards
IgorM

__________ Information from ESET Smart Security, version of virus
signature database 4798 (20100122) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4799 (20100123) __________

The message was checked by ESET Smart Security.

http://www.eset.com