Streamline PivotTable creation code

[Tod]

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

 

[Tom Ogilvy]

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).