DataPivotField

pradeepdayanand · July 16, 2010, 7:17am

what is the Alternative code for aspose for the below VBA

I use 4.9.0.0 version os ASPOSE.

Q1)

// - PENDING

// With ActiveSheet.PivotTables("PivotTable1").DataPivotField

// .Orientation = xlColumnField

// .Position = 2

// End With

Q2)

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Credit Notional"), "Credit Notional ", xlSum

Q3)

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Issuer(P)")
.Name = "Issuer (P)"
.Orientation = xlRowField
.Position = 1
End With

This message was posted using Aspose.Live 2 Forum

pradeepdayanand · July 16, 2010, 8:23am

CODE FOR REFERENCE

Sub Auto_Open()

Application.ScreenUpdating = False

If Sheets("RawData").Visible = True Then

RemoveMetricCol

End If

End Sub

Sub RemoveMetricCol()

'Deletion of Metrics Column
Dim colnum As Integer
Dim rownum As Integer
Dim RngObject As Range

Sheets("RawData").Select

Set RngObject = Cells.Find(What:="Metrics", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If RngObject Is Nothing Then
Range("A1").Select
PivotTable

Else

Cells.Find(What:="Metrics", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Selection.EntireColumn.Delete
Range("A1").Select

PivotTable

End If

End Sub

Sub PivotTable()

Dim rownum As String
Dim colnum As String
Dim UsedRange As String
Dim Reporttype As String

Sheets("Top N Gainers & Losers").Select
Reporttype = Range("A1000").Value

Sheets("RawData").Select

colnum = ActiveSheet.UsedRange.Columns.Count
rownum = ActiveSheet.UsedRange.Rows.Count

UsedRange = "RawData!R1C1:R" & rownum & "C" & colnum

Sheets("Pivot").Select
Cells.Select
Selection.Delete
Range("A1").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
UsedRange).CreatePivotTable TableDestination:= _
"Pivot!R4C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Desk")
.Orientation = xlPageField
.Position = 1
End With
Range("A4").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trader")
.Orientation = xlPageField
.Position = 1
End With
' Included to have the report date page by
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Issuer(P)")
.Name = "Issuer (P)"
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
.Name = "Period"
.Orientation = xlColumnField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total PnL"), "Total PnL ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("New/Cancel'd"), "New/Cancel'd ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Credit"), "Credit ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rates"), "Rates ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Less Rates"), "Total Less Rates ", xlSum

' If condition included to differentiate the pivot table for different reports

If Reporttype = "1" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Less Rates Less FX"), "Total Less Rates Less FX ", xlSum

ElseIf Reporttype = "2" Then

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Trading"), "Trading ", xlSum

Else

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Trading less FX"), "Trading less FX ", xlSum

End If

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Credit Notional"), "Credit Notional ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("CS01"), "CS01 ", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("IR01"), "IR01 ", xlSum

With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With

With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.RowGrand = False
End With

Sheets("RawData").Select
ActiveWindow.SelectedSheets.Visible = False

' Formatting of the Pivot table starts here

' The number format is changed to have comma seperated values
Sheets("Pivot").Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total PnL ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("New/Cancel'd ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Credit ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Rates ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Less Rates ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

' If condition included to differentiate the pivot table for different reports

If Reporttype = "1" Then

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Less Rates Less FX ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

ElseIf Reporttype = "2" Then

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trading ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

Else
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trading less FX ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

End If

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Credit Notional ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("CS01 ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("IR01 ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With

ActiveSheet.PivotTables("PivotTable1").MergeLabels = True

ActiveSheet.PivotTables("PivotTable1").PivotSelect "Daily", xlDataAndLabel, True

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With

ActiveSheet.PivotTables("PivotTable1").PivotSelect "MTD", xlDataAndLabel, True

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With

ActiveSheet.PivotTables("PivotTable1").PivotSelect "YTD", xlDataAndLabel, True

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With

Cells.Select
Cells.EntireColumn.AutoFit

With Selection.Interior
.PatternColorIndex = xlAutomatic
End With

With Selection.Font
.Name = "Arial"
.Size = 8
.Color = -10079488
End With

Range("A6:AB6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Selection.Font.Bold = True

Range("A7:AB7").Select
Selection.Font.Bold = True

Range("B5:AB5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
With Selection.Font
.Color = -10079488
End With
Selection.Font.Bold = True
Range("A1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With

Range("A2").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Range("B4").Select
Selection.Font.Bold = True

Range("A3").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With

'Descending order for values of Total Less Rates Less FX

'ActiveSheet.PivotTables("PivotTable1").PivotFields("Issuer (P)").AutoSort _
xlDescending, "Total Less Rates Less FX ", ActiveSheet.PivotTables( _
"PivotTable1").PivotColumnAxis.PivotLines(6), 1

Range("G8").Select
Selection.Sort Key1:="R8C7", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom

'Grouping the MTD and YTD metrics

ActiveWindow.Zoom = 80
Columns("B:AB").Select
Selection.ColumnWidth = 15
Rows("7:7").EntireRow.AutoFit

Columns("K:AB").Select
Selection.Columns.Group
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

'Added to left align the values under Issuer column

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Issuer (P)'[All]", _
xlLabelOnly, True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
End With

If Reporttype = "1" Then

ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"'Total Less Rates Less FX '", xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With

ElseIf Reporttype = "2" Then

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Trading '", xlDataAndLabel _
, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With

Else

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Trading less FX '", _
xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With

End If

'Keeping all the metric values in the Centre

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Total PnL ':'IR01 '", _
xlDataAndLabel, True
With Selection
.HorizontalAlignment = xlCenter
End With

Range("A2").Select

End Sub

amjad.sahi · July 16, 2010, 12:23pm

Hi,

Well, I am afraid, I don’t have much knowledge about vba, so, could not understand well your requirement for pivot table report. We still appreciate if you could manually create a sample pivot table in MS Excel (manually) and post the excel file here, we will check to see how to do it with Aspose.Cells API.

Thanks for your understanding!

pradeepdayanand · July 19, 2010, 1:40am

I cannot attach the excel file because it is against my company policy. Can the screenshots be of some help?

1) RawData - Source Information

2) Pivot - which has the pivot table - It just does some color coding on some columns and groups data based on the

pradeepdayanand · July 19, 2010, 1:42am

pradeepdayanand:

I cannot attach the excel file because it is against my company policy. Can the screenshots be of some help?

1) RawData - Source Information

2) Pivot - which has the pivot table - It just does some color coding on some columns and groups data based on the

amjad.sahi · July 20, 2010, 4:19am

Hi,

I am afraid, your desired feature of custom formatting for pivot table
items/objects are not supported at the moment. We are currently working
on reading/manipulating pivot tables in the template files.

I have logged your feature request into our issue tracking system with
an id: CELLSNET-18618.

Once we have any update we will let you know.

Thank you