Pivotcache questions

At the Uk Excel User Conference I spoke to Roger Govier and he didn't know the answers to a couple of questions about pivotcaches.
He promised to look in to them if I posted them on the forum and maybe somebody else knowes the answers or can comment on these issues.

When I create pivotcaches in code all of them have the index zero and you can only use it to create a pivottable.
Only when I create a pivottable from one of them the pivotcahe index will be uniqe.

Is this a bug and will this be fixed in SP2...?

When I have 3 pivottables in my workbook the indexes of the pivotcahes are 1,2,3
When I delete pivottable 2 the second pivotcache is also destroyed but the indexes are 1,2 and not 1,3

Why is the index of a pivotcache not fixed...?

Jelle-Jeroen Lamkamp

Hi Jelle-Jeroen,

Why would you want to create a pivotcache without a pivot table? Could you perhaps describe what exactly your scenario is?

Jan Karel

I want to switch the pivotccache from a pivottable with a new one.

like explained in http://excelusergroup.org/forums/p/1231/3807.aspx#3807

But appart from that I find it strange behaviour

Jelle-Jeroen

What is the benefit of swapping the cache as opposed to simply changing the connection and command text of the existing pivot cache(s)?

The fact that the indexes change when a pivot cache gets removed is obvious, after all, it is nothing more than an index, so if there were 3, removing one will cause a renumbering if it wasn't the last one in order.

Because I don't have a connection anymore when I want to swap.

And when I swapped a cache I want to go back without quering the database again...

So basically you are using one pivot table to show items (in turn of course) from two connections, is that what you're doing?

And you want to be able to swap the caches even when they are no longer connected to their source?

That is pretty close to what I want to do....

But I want to do it without having one or more dummy pivottables

Well, you know the answer: no cache without a pivot table, so if you want to swap two caches, you need three of them, just like when you're swapping variables in VBA.

I'm afraid you're right.

But I would be so nice if a pivotcache could have a life on its own if only for the duration when the file is open.

I haven't forgotten your question.

I have been tied up with lots of work for clients since the conference.

I will experiment and get back to you iif I can see a resolution of the problem.