Append data to pivot cache

[Jon von der Heyden]

Hi

I need to construct reports where the data easily occupies more than 65k rows (usingh xl2003). Reports are outputted as pivot tables. I have managed to understand how to create pivot cache but now I would like to know if it is possible to append data to an existing pivot cache.

I would download a new report from SAP each month and I want to append it to the cache. This is what I have as far as creating a cache is concerned:

---------------------
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> conADO <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> ADODB.Connection<br><SPAN style="color:#00007F">Dim</SPAN> recTable <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> ADODB.Recordset<br><SPAN style="color:#00007F">Dim</SPAN> strSQL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> pch <SPAN style="color:#00007F">As</SPAN> PivotCache<br><SPAN style="color:#00007F">Dim</SPAN> pivMain <SPAN style="color:#00007F">As</SPAN> PivotTable<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>strPath = ActiveWorkbook.FullName<br><br>conADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br>            "Data Source=" & strPath & ";Jet OLEDB:Engine Type=35"<br>strSQL = "Select * From [Table1]"<br><br><SPAN style="color:#00007F">Set</SPAN> recTable = <SPAN style="color:#00007F">New</SPAN> ADODB.Recordset<br><SPAN style="color:#00007F">Set</SPAN> recTable.activeconnection = conADO<br>recTable.Open strSQL<br><br><SPAN style="color:#00007F">Set</SPAN> pch = ActiveWorkbook.PivotCaches.Add(xlExternal)<br><SPAN style="color:#00007F">Set</SPAN> pch.Recordset = recTable<br><br>Worksheets.Add before:=Sheets(1)<br><SPAN style="color:#00007F">Set</SPAN> pivMain = ActiveSheet.PivotTables.Add(PivotCache:=pch, TableDestination:=Range("A1"))</FONT>

---------------------
Thanks for reading

 

[RoryA]

I don't believe you can - I think you have to rebuild the whole thing each time.

 

[Jon von der Heyden]

Thanks Rory

I'm new to this, so a few follow up questions:

1. Can I query the cache with SQL? E.g: Select * From pch
2. If so then I should be able to create a new pivot cache by Unioning the two data sets together, but how do I collect data from the external table but exclude headers/field names.
3. If I loop thru all my pivot tables and point them toward the new cache, will I lose the pivot table layout?

I know I could answer many of these by just trying but I'm hesitant to spend so much time on the code if it isn't really viable.

Cheers

 

[RoryA]

1. Nope. You can access the Recordset as a Recordset, but not query it.
2. Not much point as you can't union (see 1).
3. Theoretically no, I don't think that would be a problem, if all the same fields are available, but see 1 and 2!

 

[Jon von der Heyden]

Ouch... Show stopper!

Thanks Rory, you've saved me from spending hours on the impossible...