interacting with excel pivot using code stage

[OskarJennische2]

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
------------------------------

[NicholasZejdlik]

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
------------------------------

[OskarJennische2]

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
------------------------------

[NicholasZejdlik · Accepted solution]

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
------------------------------