Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,912
- Office Version
- 365
- Platform
- Windows
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
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
