if you use an ADODB recordset populated from the database table, you can
create an pivotcache then use this to create multiple pivot tables ...
so for an example, i have this in a code module -- do the following if
you're unsure
( ALT+F11,
Insert/Module
Tools/References Microsoft Active DataObjects 2.7 Library
)
Option Explicit
Dim pc As PivotCache
Dim rst As New ADODB.Recordset
Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim i As Long
MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
rst.Open SQL, con, adOpenStatic
Set pc = ThisWorkbook.PivotCaches.Add(xlExternal)
' create new pivot tables from the same recordset..
CreateThePivotTable "table1"
CreateThePivotTable "table2"
rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Sub
Sub CreateThePivotTable(text As String)
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets.Add()
With pc
Set .Recordset = rst
Set pt = .CreatePivotTable(ws.Range("C5"))
pt.Name = text
End With
End Sub
