Glory
I've got to agree with xenou, working with all this data spread over multiple workbooks/worksheets just sounds like trouble.
It wouldn't be too difficult to take the data from the worksheets, transfer it to Access or some other database and consolidate it there.
You could even do that with code.
Once you've got the data you could use it for the source of the pivot table, or perhaps have a look into using it for a crosstab/TRANSFORM query.
I suppose you could say a crosstab query is the database equivalent of a pivot table - I think it can actually be referred to as a pivot query.
You won't get as many options as you would have automatically with a pivot table in Excel but you can do the basics and with a little effort recreate some of the
functionality of Excel pivot tables.
If you don't want to use a query in the database you could try something like this.
Code:
Option Explicit
Sub CreatepPivotTableWithExtData()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim ptCache As PivotCache
Dim ptTbl As PivotTable
Dim ptFld As PivotField
Dim ws As Worksheet
Dim arrFields
Dim strPath As String
Dim strDBName As String
' reference to workbook pivot will go on
Set ws = ThisWorkbook.Worksheets("Accidents")
strPath = "C:\AccessStuff\"
strDBName = "TestPadDB.mdb"
With conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPath & strDBName & ";Mode=Share Deny None"
End With
conn.Open
Set cmd.ActiveConnection = conn
With cmd
.CommandText = "SELECT * FROM Accidents"
.CommandType = adCmdText
.Execute
End With
Set rst.ActiveConnection = conn
rst.Open cmd
Set ptCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set ptCache.Recordset = rst
Set ptTbl = ws.PivotTables.Add(PivotCache:=ptCache, TableDestination:=ws.Range("A1"), TableName:="CountOfAccidents")
Set ptFld = ptTbl.PivotFields("Year")
ptFld.Orientation = xlColumnField
ptFld.Position = 1
Set ptFld = ptTbl.AddDataField(ptTbl.PivotFields("Severity"), "Count", xlCount)
ptFld.Orientation = xlDataField
ptFld.Position = 1
Set ptFld = ptTbl.PivotFields("Severity")
ptFld.Orientation = xlRowField
ptFld.Position = 1
Set rst = Nothing
Set cmd = Nothing
Set conn = Nothing
End Sub
This code runs a simple query on a table in an Access database and creates a pivot table using the resulting recordset it as a data source.
By the way it's quite old code, I don't know why I used Command for the query - I think the code might have been used for something else previously.
