topic RE: interacting with excel pivot using code stage in Product Forum

interacting with excel pivot using code stage

OskarJennische2 — Wed, 19 Aug 2020 08:14:00 GMT

hello,
i am trying to interact with a simple excel pivot table. the table has a drop down list with 2 different options. i have copied the vba code to a code stage in BP using record macro in excel.

i feel like the easiest way would be to make a macro, but i dont have the permission to do that.

i am aware that i need to change the code to work with bp, but i dont know how. i am assuming i need workbook and handle handle input.
here is the code:

Sub customer_pivot()
'
' customer_pivot Macro
' for BP use only
'

'
ActiveSheet.PivotTables("PivotTableCustomerType").PivotFields( _
"[Customer_CustomerGroup].[Result].[Result]").ClearAllFilters
ActiveSheet.PivotTables("PivotTableCustomerType").PivotFields( _
"[Customer_CustomerGroup].[Result].[Result]").CurrentPage = _
"[Customer_CustomerGroup].[Result].&[Customer]"
End Sub

------------------------------
Oskar Jennische
------------------------------

RE: interacting with excel pivot using code stage

NicholasZejdlik — Wed, 19 Aug 2020 12:51:00 GMT

In the Blue Prism code stage, your first line will typically be: Dim Excel = GetInstance(handle)

You'll need to pass in the handle of course. Since you can reference Excel.ActiveSheet, there's no need to pass in the workbook name unless you prefer doing it that way. Your code would end up looking like this:

Dim Excel = GetInstance(handle)
Excel.ActiveSheet.PivotTables("PivotTableCustomerType").PivotFields("[Customer_CustomerGroup].[Result].[Result]").ClearAllFilters
Excel.ActiveSheet.PivotTables("PivotTableCustomerType").PivotFields("[Customer_CustomerGroup].[Result].[Result]").CurrentPage = "[Customer_CustomerGroup].[Result].&[Customer]"

It might be best to figure out a more generic way to do what you're doing with the pivot tables (get rid of the references to specific names and whatnot), so that way the code will be reusable in the future.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: interacting with excel pivot using code stage

OskarJennische2 — Wed, 19 Aug 2020 13:32:00 GMT

thanks for your reply. i tried pasting this in my code object with handle input, but i get the following error:

"Compiler error at line 1: 'GetInstance' is not declared. It may be inaccessible due to its protection level."

i am using ms excel extended for my other actions

------------------------------
Oskar Jennische
------------------------------

RE: interacting with excel pivot using code stage

NicholasZejdlik — Wed, 19 Aug 2020 13:52:00 GMT

GetInstance() is a method that's declared in the MS Excel VBO's global code scope (it's also in the MS Excel VBO - Extended object). The problem with creating another object is that MS Excel VBO uses a dictionary to keep track of which handle refers to which Excel application object.

If you're not using the MS Excel VBO or the MS Excel VBO - Extended object, you'll want to copy the global code from one of them to the object that you're using, and you'll also need to implement Open Instance in order create a handle for an Excel application.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------