Dynamically change part of code in Code stage

to_mas_re

New Member
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?

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
 

sahil_raina_91

Active Member
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?

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


A better approach would be to create a flag as input to code stage.
Check the flag state and decide whether you want to execute the below lines or not.
Example:

Static code
If (flag=TRUE) then
...... Line1
...... Line 2
End If


On a sidenote :
Below Bold values can also be an input to code stage.
.Orientation = 4
.Position = 1
.Function = -4112
.Caption = "Count of Column2"
 

to_mas_re

New Member
A better approach would be to create a flag as input to code stage.
Check the flag state and decide whether you want to execute the below lines or not.
Example:

Static code
If (flag=TRUE) then
Line1
Line 2
End If


On a sidenote :
Below Bold values can also be an input to code stage.
.Orientation = 4
.Position = 1
.Function = -4112
.Caption = "Count of Column2"

Thank you for response @sahil_raina_91 !
I think there is a misunderstanding. I need to run the second part of the code (everything below 'PART TWO' title), but I want to change the lines and values.

The code that characterize Pivot Table layout has several segments that look like this:

Code:
With ws.PivotTables("PivotTable1").PivotFields("Column1")
.Attribute1 = ...
.Attribute2 = ...
.Attribute3 = ...
End With

To fully characterize my Pivot Table I need to write several of these segments with different values for attributes. Therefore, I am looking for option to send these portions of code into Code Stage as parameters/variables.
 

sahil_raina_91

Active Member
Thank you for response @sahil_raina_91 !
I think there is a misunderstanding. I need to run the second part of the code (everything below 'PART TWO' title), but I want to change the lines and values.

The code that characterize Pivot Table layout has several segments that look like this:

Code:
With ws.PivotTables("PivotTable1").PivotFields("Column1")
.Attribute1 = ...
.Attribute2 = ...
.Attribute3 = ...
End With

To fully characterize my Pivot Table I need to write several of these segments with different values for attributes. Therefore, I am looking for option to send these portions of code into Code Stage as parameters/variables.

I don't think you will be able to change the number of attributes to be applied on the PIVOT table in a single code stage at runtime.
From what I understand, you are looking to compile and execute the code at run time, which in itself is not straightforward (It is somewhat possible but with limitations)
Of course you can hard code all maximum possible attributes in a single code stage, and based on inputs apply only those you wish.
For example :
Have a code stage having maximum possible attributes (assume 6 here)
Attribute1 = A
.Attribute2 = B
.Attribute3 = C
.Attribute4 = D
.Attribute5 = E
.Attribute6 = F

A,B,C,D,E,F becomes INPUT for the code stage
If you only want to apply 1,3 and 6 , keep values of 2,4 and 5 AS BLANK
and have something like this in the code
If (B<>"")
Attribute2 = B
End If
So on . . . . .
____________________________________________________________________________________
If you are able to find a better way to achieve this, I will be extremely interested to know how.
 

to_mas_re

New Member
Even though there was no direct answer to my question about dynamic changes to parts of code in Code Stage, my initial problem was solved thanks to @sahil_raina_91 hint.

I was able to create general Action to create Pivot table. Here is my solution:

1. Duplicate Action "Activate Worksheet" and change name (my name is "Create Pivot Table")
2. Change code stage. Add these lines and new input "range" (see img1 and img2):
Code:
rng = range
Dim PCache as object = wb.PivotCaches.Create(SourceType:=1, SourceData:= rng).CreatePivotTable _
(TableDestination:=ws.Range("B2"), TableName:="PivotTable1")
3. Create Collection "PTStructure" with given fields (see img3)
4. Loop through the Collection and create code stage with these lines:
Code:
Dim wb as Object = GetWorkbook(handle,workbookname)
Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname)

If Len(ToString(orientation))=0 Then Throw New Exception("Orientation is mandatory parameter")
If Len(ToString(position))=0 Then Throw New Exception("Position is mandatory parameter")

With ws.PivotTables(tablename).PivotFields(pivotfield)
.Orientation = orientation
.Position = position
If fnctn<>0 Then .Function = fnctn
If calculation<>0 Then .Calculation = calculation
If caption<>"" Then .Caption = caption
End With

This is so far the best I can do. I believe there are few things that can be implemented better, but ... whatever.
Hope this was helpfull.
 

Attachments

  • img1.png
    img1.png
    16 KB · Views: 9
  • img2.png
    img2.png
    21.3 KB · Views: 9
  • img3.png
    img3.png
    10.9 KB · Views: 8
  • img4.png
    img4.png
    18.4 KB · Views: 10
  • img5.png
    img5.png
    24.7 KB · Views: 8
  • img6.png
    img6.png
    33.6 KB · Views: 8
Top