PDA

View Full Version : [SOLVED] Pivot Table Creation with Optional Arguments



rajkumar
10-05-2013, 12:01 AM
Hi,

I need support in creating pivot with a code base, below is the code that i am using to create pivot. It works perfectly as expected for one parameter for row field or column field etc. But now i need to create a pivot with multiple row fields and column fields and data fields.

If anyone can support on how to pass arguments for multiple values for one parameter, it would be of great help

Thanks
Raj :)


Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowField As String, ColField As String, DataField As String, PageField As String)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable _
TableDestination:=Worksheets(SourceSheetName).Range("A1"), TableName:=SourceTableName
With ActiveSheet.PivotTables(SourceTableName).PivotFields(ColField)
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables(SourceTableName).PivotFields(RowField)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(SourceTableName).PivotFields(DataField)
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.NumberFormat = "0"
End With
With ActiveSheet.PivotTables(SourceTableName).PivotFields(PageField)
.Orientation = xlPageField
.Position = 1
.EnableMultiplePageItems = True
End With
With Worksheets(SourceSheetName).PivotTables(SourceTableName)
.TableStyle2 = "PivotStyleDark5"
End With

End Sub

Paul_Hossler
10-06-2013, 02:32 PM
With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER01")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER02")
.Orientation = xlRowField
.Position = 2 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End With
With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER03")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("SamplePivotTable").AddDataField ActiveSheet. _
PivotTables("SamplePivotTable").PivotFields("HEADER04"), "Sum of HEADER04", _
xlSum
ActiveSheet.PivotTables("SamplePivotTable").AddDataField ActiveSheet. _
PivotTables("SamplePivotTable").PivotFields("HEADER05"), "Sum of HEADER05", _
xlSum
With ActiveSheet.PivotTables("SamplePivotTable").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With



Probably just need to use .Position = 2, = 3, etc.

Paul

Aflatoon
10-07-2013, 12:38 AM
You can pass arrays of the names of the fields:
Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowFields() As String, _
ColFields() As String, DataFields() As String, PageFields() As String)

Dim n As Long
Dim pt As PivotTable

Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(SourceSheetName).Range("A1"), TableName:=SourceTableName)

With pt
For n = LBound(ColFields) To UBound(ColFields)
With .PivotFields(ColFields(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
For n = LBound(RowFields) To UBound(RowFields)
With .PivotFields(RowFields(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n
For n = LBound(DataFields) To UBound(DataFields)
With .PivotFields(DataFields(n))
.Orientation = xlDataField
.Position = n + 1
.Function = xlCount
.NumberFormat = "0"
End With
Next n
For n = LBound(PageFields) To UBound(PageFields)
With .PivotFields(PageFields(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n
.TableStyle2 = "PivotStyleDark5"
End With

End Sub

Aflatoon
10-07-2013, 12:51 AM
Perhaps better - this will allow you to pass either an array or a single value as the data for any or all of the fields:

Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowFields, _
ColFields, DataFields, PageFields)

Dim n As Long
Dim pt As PivotTable
Dim vRows, vCols, vData, vPages

Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(SourceSheetName).Range("A1"), TableName:=SourceTableName)

With pt

.ManualUpdate = True

If IsArray(ColFields) Then
vCols = ColFields
Else
vCols = Array(ColFields)
End If
If IsArray(RowFields) Then
vRows = RowFields
Else
vRows = Array(RowFields)
End If

If IsArray(DataFields) Then
vData = DataFields
Else
vData = Array(DataFields)
End If

If IsArray(PageFields) Then
vPages = PageFields
Else
vPages = Array(PageFields)
End If

For n = LBound(vCols) To UBound(vCols)
With .PivotFields(vCols(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
For n = LBound(vRows) To UBound(vRows)
With .PivotFields(vRows(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n

For n = LBound(vData) To UBound(vData)
With .PivotFields(vData(n))
.Orientation = xlDataField
.Position = n + 1
.Function = xlCount
.NumberFormat = "0"
End With
Next n

For n = LBound(vPages) To UBound(vPages)
With .PivotFields(vPages(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n

.TableStyle2 = "PivotStyleDark5"

.ManualUpdate = False
End With

End Sub

rajkumar
10-07-2013, 06:20 AM
Hi Aflatoon / Paul,

Many thanks, for your reply. Works perfect as needed. I needed one more small change as below, will it be possible to accommodate the same.


Sub MakePivotCriteriaCount(SourceSheetName As String, SourceTableName As String, RowField As String, _
ColField As String, DataField As String, PageField As String, ArithOps As Boolean)
With ActiveSheet.PivotTables(SourceTableName).PivotFields(DataField)
.Orientation = xlDataField
.Position = 1
If ArithOps = True Then
.Function = xlCount
.Function = xlSum
End If
.NumberFormat = "0"
End With

Raj

Aflatoon
10-07-2013, 06:29 AM
Yes - do give it a try and let us know if you have any problems. ;)

Paul_Hossler
10-07-2013, 07:06 AM
I think you need to pick one or the other. As it is, it will always be xlSum. Also it will only apply to the first data field, which may or may not be what you want




.Orientation = xlDataField
.Position = 1

If ArithOps = True Then
.Function = xlCount
.Function = xlSum
End If





Paul

rajkumar
10-07-2013, 07:06 AM
Yes - do give it a try and let us know if you have any problems. ;)

I think my question was not clear, i was asking how to declare .Function parameter for each DataField. may be some one will be sum and another will count. I am not clear on how to pass these within array values.

And suppose if situation is i do not have column field but have one row field and two data field. How to skip over the column field argument.

I have these queries, can you help?


Call MakePivotCriteria1(PiSheet, "A1", "OPEN_CALLS", "SSP NAME", , ("SR No." "Units Used"), "Bus Stream") '<=== is it correct?

' below is one of the situations as example:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"OPEN_CALLS").CreatePivotTable _
TableDestination:=Worksheets(PiSheet).Range("G1"), TableName:="OPEN_CALLS1"
With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("SSP NAME")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("SR No.")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.NumberFormat = "0"
End With
With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("Units Used")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "0"
End With
With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("Bus Stream")
.Orientation = xlPageField
.Position = 1
.EnableMultiplePageItems = True
End With
With Worksheets(PiSheet).PivotTables("OPEN_CALLS1")
.TableStyle2 = "PivotStyleDark5"
End With

Raj

Aflatoon
10-07-2013, 08:15 AM
I think my question was not clear

I think you're right - perhaps because it appears to be ever-changing.

In all honesty, by the time you have made this code completely generic, it will be easier to simply write the code to create the pivot table you want each time, than it will to write the code to call this routine.

rajkumar
10-07-2013, 08:39 PM
Hi Aflatoon,
I have tried some more to modify the code, but i am getting this error while calling it. Can you help me to complete it.
10670
Calling the sub

Call MakePivotCriteria1(PiSheet, "G1", "OPEN_CALLS", Array("SSP NAME"), Array(), Array("SR No.", "Task Desc"), Array("Bus Stream"), Array(True, True))

Sub for making pivot

Sub MakePivotCriteria1(DestSheetName As String, PlacementCell As String, SourceTableName As String, _
RowFields, ColFields, DataFields, PageFields, ArithOps)

Dim n As Long
Dim pt As PivotTable
Dim vRows, vCols, vData, vPages
'------------------------------
Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(DestSheetName).Range(PlacementCell), TableName:=SourceTableName)
'------------------------------------------------------------------------------------------------------------------
With pt
.ManualUpdate = True
'----------------------
If IsArray(RowFields) Then
vRows = RowFields
Else
vRows = Array(RowFields)
End If
'----------------------
For n = LBound(vRows) To UBound(vRows)
With .PivotFields(vRows(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n
'--------------------------
If IsMissing(ColFields) Then
GoTo Action1
End If
'--------------------------
If IsArray(ColFields) Then
vCols = ColFields
Else
vCols = Array(ColFields)
End If
'-------------------------------------
For n = LBound(vCols) To UBound(vCols)
With .PivotFields(vCols(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
'--------------------------
Action1: If IsArray(DataFields) Then
vData = DataFields
Else
vData = Array(DataFields)
End If
'-------------------------------------
For n = LBound(vData) To UBound(vData)
With .PivotFields(vData(n))
.Orientation = xlDataField
.Position = n + 1
If ArithOps = True Then
.Function = xlCount
Else
.Function = xlSum
End If
.NumberFormat = "0"
End With
Next n
'--------------------------
Check2: If IsMissing(PageFields) Then
GoTo Action2
End If
If IsArray(PageFields) Then
vPages = PageFields
Else
vPages = Array(PageFields)
End If
'----------------------
For n = LBound(vPages) To UBound(vPages)
With .PivotFields(vPages(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n
'-------------------------------------
Action2: .TableStyle2 = "PivotStyleDark5"
.ManualUpdate = False
End With
End Sub

Aflatoon
10-08-2013, 01:07 AM
Try this:
Sub MakePivotCriteria1(DestSheetName As String, PlacementCell As String, SourceTableName As String, _
Optional RowFields, Optional ColFields, Optional DataFields, Optional PageFields, Optional ArithOps)

Dim n As Long
Dim pt As PivotTable
Dim vRows As Variant
Dim vCols As Variant
Dim vData As Variant
Dim vPages As Variant
'------------------------------
Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(DestSheetName).Range(PlacementCell), TableName:=SourceTableName)
'------------------------------------------------------------------------------------------------------------------
With pt
.ManualUpdate = True
'----------------------
If Not IsMissing(RowFields) Then
If IsArray(RowFields) Then
vRows = RowFields
Else
vRows = Array(RowFields)
End If
'----------------------
For n = LBound(vRows) To UBound(vRows)
With .PivotFields(vRows(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n
End If
'--------------------------
If Not IsMissing(ColFields) Then
'--------------------------
If IsArray(ColFields) Then
vCols = ColFields
Else
vCols = Array(ColFields)
End If
'-------------------------------------
For n = LBound(vCols) To UBound(vCols)
With .PivotFields(vCols(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
End If
'--------------------------
If Not IsMissing(DataFields) Then
If IsArray(DataFields) Then
vData = DataFields
Else
vData = Array(DataFields)
End If
'-------------------------------------
For n = LBound(vData) To UBound(vData)
With .PivotFields(vData(n))
.Orientation = xlDataField
.Position = n + 1
.Function = ArithOps(n)
.NumberFormat = "0"
End With
Next n
End If
'--------------------------
If Not IsMissing(PageFields) Then
If IsArray(PageFields) Then
vPages = PageFields
Else
vPages = Array(PageFields)
End If
'----------------------
For n = LBound(vPages) To UBound(vPages)
With .PivotFields(vPages(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n
End If
'-------------------------------------
.TableStyle2 = "PivotStyleDark5"
.ManualUpdate = False
End With
End Sub



Called like this:

Call MakePivotCriteria1(PiSheet, "G1", "OPEN_CALLS", "SSP NAME", , Array("SR No.", "Task Desc"), "Bus Stream", Array(xlsum, xlsum))
for flexibility I changed the last argument to be the actual function you want used.

rajkumar
10-08-2013, 06:14 AM
Works Perfect!! Thanks a Ton

I could reduce add-in file size by 2mb, thanks again

Raj