PDA

View Full Version : Found some interesting code:



Djblois
05-08-2007, 10:58 AM
I found this code browsing through the help files:

Worksheets(1).PivotTables("Pivot1") _
.PivotCache.OptimizeCache = True


Unfortunately, I can't get it to work. I tried looking all through the help to figure out how to use it but it just says use it to optomize PivotCache when its constructed. I have 2 questions:

1) Theoratically should this speed up the creation of pivot tables?

2) Why won't it work?

I tried it before the pivotcache was created and after it was created. they both give me the Application defined or object defined error.

Here is my full code:

Set pRange = workingBook.Cells(1, 1).Resize(finalRowDetail, finalColumnDetail)
PT.PivotCache.OptimizeCache = True
Set ptCache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pRange.Address)
Set PT = ptCache.CreatePivotTable(TableDestination:=pvt.Range("A1"), TableName:="PivotTable")

Norie
05-08-2007, 12:02 PM
Why are you setting a reference to the pivot table after this?

PT.PivotCache.OptimizeCache = True

Djblois
05-08-2007, 12:05 PM
thanks. my mistake but I also tried it after the reference. Do you know if that should speed up a pivot table?

Andy Pope
05-09-2007, 04:18 AM
The remark in the help file is not too promising.

Remarks
Cache optimization results in additional queries and degrades initial performance of the PivotTable report.

Djblois
05-09-2007, 07:58 AM
lol Thanks Andie. I know that is why I am confused. I am starting to think it only works with external data but if anyone knows differently please tell me.