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 are: xlConsolidation, xlDatabase, xlExternal, xlPivotTable, and
> > xlScenario but I don't see which would accept an array as input.
> >
> > Any ideas!
> >
> >
> >
> >
> >
|
Similar Threads
