cancel
Showing results for 
Search instead for 
Did you mean: 

MS Excel Pivot Table Filtering

TheodorosPapaso
Level 3
Hi All,
I'm new in BluePrism and I have the following scenario. There is an excel with 2 pivot tables. I need to change the filter, apply refresh and save the excel with the updated values in order to send as an attachment via email. Object MS Excel VBO - Extended doesn't seem to work properly for that work (I understand 'Add Criteria to Page Field for Pivot Table' action is only for creation and not in an already created pivot?), therefore I ended up trying my lock with Code stage. Unsuccessfully though so far. RefreshTable seems to work fine if I go first in the excel and uncheck the option 'Refresh data when opening the file', otherwise it gives me an error while opening the workbook. The problem is that the excel is exported with 'Refresh data when opening the file' checked. 
I tried the below code but no luck so far:

Dim ws As Object
ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Activate()
ws.AutoFilterMode = False
ws.PivotTables(PivotTableName).PivotFields(Field).CurrentPage = Criteria
ws.PivotTables(PivotTableName).RefreshTables

Input Parameters:
Handle (Number) = 1
Workbook (Text) - The name of the workbook
Worksheet (Text) - The name of the worksheet
PivotTableName (Text) - The name of the pivot table
Field (Text) - The name of the field that filter applies
Criteria (Text) - The value for the filter that I want to apply

Any Ideas are most welcome! Thanks in advance!
#pivottable​​ #pivotfilter #pivotrefresh​​​

------------------------------
Theodoros Papasotiriou
------------------------------
11 REPLIES 11

Hi Gopal,
Apologies for replying late. Unfortunately even with the library added it didn't work because on Open Workbook, it's giving me the error. For the record, eventually I implemented it using Recover-Resume, catching and skipping the error.
Thanks for your help!

------------------------------
Theodoros Papasotiriou
------------------------------

NP, Glad you could make it work 👍

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------