PDA

View Full Version : PivotCache fails?



theta
03-01-2011, 07:14 AM
Hi

I am trying to create a pivottable programatically using VBA. Works fine on smaller data sets. On larger data sets it fails. Any ideas? Or how to do it another way so it doesn't break :)



Private Function CreateXMLpivots(owb As Workbook, oWS As Worksheet) As Boolean
'Purpose: Create Pivots from source XML data
Dim oPC As PivotCache
Dim oPT As PivotTable

Dim nLastRow As Long, nLastCol As Long

' On Error Resume Next 'GoTo ErrHandler

nLastRow = Last("Row", oWS.UsedRange)
nLastCol = Last("Col", oWS.UsedRange)

MsgBox (nLastRow & " Rows " & nLastCol & " Columns")

Set oPC = owb.PivotCaches.Create(xlDatabase, oWS.Range(oWS.Cells(1, 1), oWS.Cells(nLastRow, nLastCol)))

owb.Worksheets.Add(after:=owb.Worksheets(owb.Worksheets.Count)).Name = "HPMN"
owb.Sheets("HPMN").Tab.Color = 49407

Set oPT = oPC.CreatePivotTable(owb.Sheets("HPMN").Range("A1"), "HPMN Codes", True)

With oPT
.DisplayFieldCaptions = True
.ColumnGrand = True
.SaveData = False
End With

'Add Data fields first, otherwise RowField is replaced
oPT.AddDataField field:=oPT.PivotFields("TapSeqNo"), Function:=xlMin
oPT.AddDataField field:=oPT.PivotFields("TapSeqNo"), Function:=xlMax
oPT.AddDataField field:=oPT.PivotFields("TotalNetCharge"), Function:=xlSum
oPT.AddDataField field:=oPT.PivotFields("TotalTax"), Function:=xlSum
oPT.AddFields RowFields:=oPT.PivotFields("HPMN").Name
oPT.TableRange1.Columns(2).NumberFormat = "#,###,##0"
oPT.TableRange1.Columns(3).NumberFormat = "#,###,##0"
oPT.TableRange1.Columns(4).NumberFormat = "#,###,##0.00"
oPT.TableRange1.Columns(5).NumberFormat = "#,###,##0.00"
owb.Sheets("HPMN").Range("A3").Select
ActiveWindow.FreezePanes = True

If owb.Sheets("HPMN").PivotTables.Count > 0 Then
frmWait.lbxInfo.AddItem "HPMN Pivot created"
CreateXMLpivots = True
Else
frmWait.lbxInfo.AddItem "*** HPMN Pivot NOT created ***"
CreateXMLpivots = False
End If
TheEnd:
Set oPC = Nothing
Set oPT = Nothing
Exit Function
ErrHandler:
Call LogErr("CreateXMLpivots", Err.Number, Err.Description)
GoTo TheEnd
End Function