topic RE: Dynamically change part of code in Code stage in University Forum

Dynamically change part of code in Code stage

to_mas_re — Wed, 16 Sep 2020 13:38:00 GMT

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?

'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

------------------------------
to_mas_re
------------------------------

RE: Dynamically change part of code in Code stage

shashank.kumar280 — Wed, 16 Sep 2020 15:59:00 GMT

Hi Tomas,

You can have a input variable for the code stage whose value you will be able to access inside the code. Based on this variable you can have a If/else statement block and put the relevant code in the desired block.

Sample code stage snapshot with If condition-

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Dynamically change part of code in Code stage

to_mas_re — Thu, 17 Sep 2020 11:23:00 GMT

Hi,

thanks for response. Could you please further explain this approach? Maybe use my case as an example.

My point is that I want developer to define everything about the Pivot Table in one code, that he can then send to this code stage and create the table. There is a lot of attributes to PivotFields that developer can define and cannot imagine to use If Else statement to cover all of them (or at least the most important).

------------------------------
Tomas Rehak
RPA Developer
VUB Slovensko
Europe/Bratislava
------------------------------

RE: Dynamically change part of code in Code stage

shashank.kumar280 — Thu, 17 Sep 2020 13:30:00 GMT

Hi,

There is a MS Excel Extended asset on Digital Exchange providing Pivot Table related actions. I would suggest to see if that meets the requirements. Additionally the code can be further customized.
https://digitalexchange.blueprism.com/dx/entry/9648/solution/ms-excel-vbo---extended

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Dynamically change part of code in Code stage

to_mas_re — Thu, 17 Sep 2020 13:46:00 GMT

I am glad I could spent two days on something that already exists -_- Don't know how I was not able to find this before.

BUT! Since I wanted to create general Action for creating Pivot Table, here is my solution (big thanks goes to sahil_raina_91 on rpaforum).

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.

------------------------------
Tomas Rehak
RPA Developer
VUB Slovensko
Europe/Bratislava
------------------------------