Create a PivotTable from a VBA array - help!
 

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!

Create a PivotTable from a VBA array - help!
 

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!

Create a PivotTable from a VBA array - help!
 

PS My test data was 5244x7
and this routine worked pretty fast

"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!

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!