Can I use an array as pivot table source data?

[Bagharmin]

Currently, my code takes data from the master table, creates a secondary table on another sheet, then refreshes the source data range of the Pivot Table and Pivot Chart that are on a 3rd sheet. I've done this since, based on the way the data in the master table is structured (and changing the layout isn't an option), I can't create a Pivot Table and Chart that show the data the way I need. My question is, instead of my code spitting out the raw data onto a second sheet so I can create the table I want, can I dump the data into an array and have VBA create the Pivot Table and Chart (instead of just refreshing it) using that array as the source data? If I did that, would I run into problems if someone wanted to change the look of the Pivot Table/Chart?

Just looking for general input and guidance. Thanks so much.

 

[Worf]

If your data has a modest size, the pivot table source data can be a worksheet table, I see no disadvantages in that.
VBA can create a pivot table and chart from the sheet table.
With big data, it may be interesting to create the pivot from an external database. The code below produces a pivot from an Access database. It’s an example where the source data is not in any sheet.

Sub FromAccess()
Dim wb As Workbook
Set wb = ThisWorkbook


wb.Connections.Add2 "Campaign_Template", "A sample database table.", Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Eddie\Desktop\Campaign_Template.mdb;Mode=Share " _
, _
"Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
, _
"DB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet " _
, _
"OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Loca" _
, _
"le on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet " _
, _
"OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"), "Campaign_Table", 3


wb.PivotCaches.Create(SourceType:=xlExternal, SourceData:=wb.Connections("Campaign_Template"), Version:= _
xlPivotTableVersion15).CreatePivotTable TableDestination:="Plan7!R20C8", _
TableName:="Pivot4", DefaultVersion:=xlPivotTableVersion15


With ActiveSheet.PivotTables("Pivot4").PivotFields("City")
    .Orientation = xlPageField
    .Position = 1
End With
End Sub

 

[pgc01]

Hi

I don't think you can build a pivot table directly from an array.

The closest I see, not using tables or ranges in the worksheet, just vba, is to use an ADO disconnected recordset (a bit like a database table but without the database ).

You'd load the values of your array into the recordset. A pivot table accepts a recordset as the data source, so you should be good.


Not sure how efficient this is is but I tested something like this some years ago (not sure I posted it) and it worked OK.

If you really don't want any auxilliary table this is something you can explore.