Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamline PivotTable creation code
I have some tried and true code that creates a pivot table
using ADO to get data from an Access database. I use this code in all workbooks that have pivot table reports. Works great. So what I'm doing now is looking where I can reduce the amount of code and take out unneeded lines, etc. I have this one section: sql = "Select * From qryName" cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Database Folder\Database Name.mdb;" rs.Open sql, cn Set PC = ThisWorkbook.PivotCaches.Add (SourceType:=xlExternal) Set PC.Recordset = rs Set PT = PTSheet.PivotTables.Add(PivotCache:=PC, TableDestination:=SheetName.Range("A9")) With PT '...... work with the pivot table This connects to the database, runs my query and creates the results in a recordset. Then it creates a pivotcache and puts the recordset in the pivotcache. Then it creates the pivottable from the pivotcache. Could I eliminate a step and just have the pivotcache create from the connection, or is this about the best way to do it. Suggestions? tod |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamline PivotTable creation code
If you have a query in Access that creates your recordset, or you can do it
with an sql statement, then I believe you should be able to create the pivot cache directly from the table. Easiest way would be to do it manually with the recorder on to see the code. But I am sure you can create it without going through ADO (which wasn't an option in xl97 I am pretty sure). -- Regards, Tom Ogilvy "Tod" wrote in message ... I have some tried and true code that creates a pivot table using ADO to get data from an Access database. I use this code in all workbooks that have pivot table reports. Works great. So what I'm doing now is looking where I can reduce the amount of code and take out unneeded lines, etc. I have this one section: sql = "Select * From qryName" cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Database Folder\Database Name.mdb;" rs.Open sql, cn Set PC = ThisWorkbook.PivotCaches.Add (SourceType:=xlExternal) Set PC.Recordset = rs Set PT = PTSheet.PivotTables.Add(PivotCache:=PC, TableDestination:=SheetName.Range("A9")) With PT '...... work with the pivot table This connects to the database, runs my query and creates the results in a recordset. Then it creates a pivotcache and puts the recordset in the pivotcache. Then it creates the pivottable from the pivotcache. Could I eliminate a step and just have the pivotcache create from the connection, or is this about the best way to do it. Suggestions? tod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivottable code problem | Excel Discussion (Misc queries) | |||
big list. Need advice for a new user of how to streamline it | New Users to Excel | |||
How to streamline this Formula? | Excel Discussion (Misc queries) | |||
streamline SUMPRODUCT | Excel Worksheet Functions | |||
code for creation of multiple pivot tables | Excel Programming |