excel pivot table drop down list?

[HickeHiskelig]

hello
i was assigned with a new process. this time i am accessing a huge excel file with a lot of data. the first step is to choose 1 out of 2 options in a pivot table to filter the huge data. i believe it is possible to create a macro in excel that changes the pivot table and then use "run macro". the problem is that i dont have access to change and create macros in this excel document.

is it another to do it? thanks

 

[HickeHiskelig]

i have now made a personal macro and copied the code into a code action in BP. i have no idea on how to modify the code to make it work with BP. i suppose i need handle and worksheet inputs

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

 

[sahil_raina_91]

In the same Excel VBO you are currently using, create a new action.

Create 3 Input Data Items for Code stage : Handle(number) , Workbook(text) , Worksheet(text)
Create 2 Output Data Items for Code stage : Success(flag) , Message(text)
Paste this code inside code stage :


Dim ws As Object
Try
ws = GetWorkSheet(Handle, Workbook, Worksheet)

ws.PivotTables("PivotTableCustomerType").PivotFields( _
"[Customer_CustomerGroup].[Result].[Result]").ClearAllFilters
ws.PivotTables("PivotTableCustomerType").PivotFields( _
"[Customer_CustomerGroup].[Result].[Result]").CurrentPage = _
"[Customer_CustomerGroup].[Result].&[Customer]"

Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
ws = Nothing
End Try

 

[edwards142]

Recently I have learned how to create drop-down in Excel so I can share link of that informative post from which I got the help.
source: Create Drop-Down List In Excel