Hi,
I have a Code stage that creates Pivot Table in Excel. The first part of the code is general and the second part is there to describe how the Pivot Table should look like. I would like to be able to dynamically change the second part whenever I want to change the Pivot Table layout. How to do that?
Here is my example. I would like to create the PART TWO in Process Studio and send it to Code Stage as a variable or something like that. Is there any way?
I have a Code stage that creates Pivot Table in Excel. The first part of the code is general and the second part is there to describe how the Pivot Table should look like. I would like to be able to dynamically change the second part whenever I want to change the Pivot Table layout. How to do that?
Here is my example. I would like to create the PART TWO in Process Studio and send it to Code Stage as a variable or something like that. Is there any way?
Code:
'PART ONE'
--------------
Dim rng as Object
Dim wb as Object = GetWorkbook(handle,workbookname)
Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname,createifmissing)
sheetexists = ws IsNot Nothing
If sheetexists then ws.Activate()
rng = range
Dim PCache as object = wb.PivotCaches.Create(SourceType:=1, SourceData:= rng).CreatePivotTable _
(TableDestination:=ws.Range("B2"), TableName:="PivotTable1")
'PART TWO'
--------------
With ws.PivotTables("PivotTable1").PivotFields("Column1")
.Orientation = 1
.Position = 1
End With
with ws.PivotTables("PivotTable1").PivotFields("Column2")
.Orientation = 4
.Position = 1
.Function = -4112
.Caption = "Count of Column2"
End With