Creating an older version pivottable

I

IgorM

Hi

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

Kind regards
IgorM
 
R

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

IgorM said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top