Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a pivottable from a recordset? | Excel Programming | |||
Create a Pivottable in a macro | Excel Discussion (Misc queries) | |||
function to reference all of pivottable data field array | Excel Worksheet Functions | |||
How to create a Pivottable from Textfile | Excel Programming | |||
Create a PivotTable from a VBA array? | Excel Programming |