Create a PivotTable from a VBA array - help!
Hi Patrick -
While inspired, I recognize my limitations. I am an experienced programmer
learning VBA. To be honest, this is beyond my current skill set. I have
saved the solution to my "good ideas" folder for later use when I am move
familiar with the techniques you applied.
I need to be able to maintain this code so I have opted to do as much as I
can in the array work area and then output to "work ranges" in the format
best structured to the pivot tables I want to execute. Undoubtedly this will
have slower overall throughput but I can live with that until I can better
understand your solution.
INSPIRED - yes!
Thanks,
Ken
"Patrick Molloy" wrote:
I wasn't able to do this.
The only think that I could do was first to build a recortset object , then
add each field of my array into it. It worked ok ...however, I did need to
'fix ' the data
running TEst does this ...it simulates your array by loading data into
variant object. this is passed to a sub thats creates the pivot. To do this,
the BeCreative sub passes the data to a function taht returns a recoprdset,
which can easily be loaded to a pivot table.
I hope this inspires you. Let me know
Option Explicit
Sub test()
Dim data As Variant
data = Range("pivot.data")
BeCreative data
End Sub
Sub BeCreative(mydata As Variant)
Dim pc As PivotCache
Dim pt As PivotTable
Dim rst As Recordset
Set rst = GetRecordSet(mydata)
With ActiveWorkbook.PivotCaches.Add(xlExternal)
Set .Recordset = rst
.CreatePivotTable Range("C5"), "MyPivotdata"
End With
End Sub
Function GetRecordSet(data As Variant) As Recordset
Dim rs As Recordset
Dim index As Long, col As Long
Set rs = New Recordset
' 1st row is headers
With rs.Fields
For col = LBound(data, 2) To UBound(data, 2)
.Append data(1, col), adVarChar, 25
Next
End With
' add records skipping row 1
rs.Open
For index = LBound(data, 1) + 1 To UBound(data, 1)
rs.AddNew
For col = LBound(data, 2) To UBound(data, 2)
rs.Fields(col - 1) = data(index, col)
Next
Next
Set GetRecordSet = rs
End Function
"KG Old Wolf" wrote:
I want to run multiple pivot tables off an array exceeding 500k cells. I
believe it would vastly improve performance. I've tried variations of the
code below to no avail.
Set Pivot_Cache_01 = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Array_Work(1,1),(Rows_Count, Columns_Count))
I know there a xlConsolidation, xlDatabase, xlExternal, xlPivotTable, and
xlScenario but I don't see which would accept an array as input.
Any ideas!
|