Creating an older version pivottable
 

Hi

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

Kind regards
IgorM

Creating an older version pivottable
 

Hi Igor

in XL2007 the code would look something like
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, 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").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Value"), "Sum of Value", xlSum

For XL2003 the code would be
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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").AddDataFiel d
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:=xlDa tabase
to
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase

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

--
Regards
Roger Govier

"IgorM" wrote in message
...
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