Is it possible to clone a pivotcache recordset?

[Jon von der Heyden]

Hi

Does anyone know how to create a new recordset as an exact copy of an existing pivot cache recordset? I can't access the external data that the pivot table is created from. I would like to either create a new recordset that is an exact copy, or output the entire existing recordset to a text file. I have tried the following:

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] CloneRecordSet()
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADODB.Recordset
    [COLOR=blue]Dim[/COLOR] pvc [COLOR=blue]As[/COLOR] PivotCache
 
    [COLOR=blue]Set[/COLOR] pvc = Sheet4.PivotTables(1).PivotCache
    [COLOR=blue]Set[/COLOR] recData = pvc.Recordset.Clone(adLockUnspecified) [COLOR=red]'error on this line[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Application-defined or object-defined error.

I have also tried:

Set recData = pvc.Recordset

 

[Jon von der Heyden]

Since the board has picked up a bit: BUMP

Put it another way:
Assume you have a pivot table that was built from an Access table. That Access table gets deleted. How do you retrieve the full table of data from the pivot table since all of it should be stored in the pivot cache. Bearing in mind that the record count exceeds the # of rows (XL03).

Any idea?

EDIT: I'm thinking a stepped approach by taking segments from the pivot table using ShowDetail method is possible. But I'm hoping I can simply collect the recordset somehow instead.

 

[Richard Schollar]

Jon

I think the stepped approach is probably a good bet if you can't get the xml suggestion to work. I just tested it on a small pivot table and it worked pretty well.

Since this is an oldish question did you finally solve this issue?

 

[Jon von der Heyden]

I liked your suggestion of getting it from the XML file. On the face of it, it seems that all my data is there. So thanks for that!

I haven't pulled all the data out yet because I've given my time to rebuilding the DB tables etc. I think I'll start work on the data tomorrow.