topic RE: MS Excel Pivot Table Filtering in Digital Exchange

MS Excel Pivot Table Filtering

TheodorosPapaso — Tue, 01 Jun 2021 11:47:00 GMT

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Tue, 01 Jun 2021 12:55:00 GMT

Hi Theodoros,

What error do you get when you try to open the file with your code?

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

RE: MS Excel Pivot Table Filtering

TheodorosPapaso — Tue, 01 Jun 2021 13:12:00 GMT

Hi Gopal and thanks for the quick reply. I get "Internal: Could not execute code stage because exception thrown by code stage: Unable to get the PivotFields property of the Pivot Table class"

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Tue, 01 Jun 2021 17:40:00 GMT

Hi Theodoros,

While I checked stackoverflow for the error it indicates that the error is due to incorrect name of field but according to you it works when Refresh is turned off. So maybe you can try this before changing the Criteria and the turn it back to true at the end. Not sure if that'll work

ws.PivotTables("PivotTable2").PivotCache.RefreshOnFileOpen = False

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

RE: MS Excel Pivot Table Filtering

TheodorosPapaso — Wed, 02 Jun 2021 09:14:00 GMT

Hi Gopal,

Eventually it worked with the "Excel VBO-Extended" action "Add Criteria to Page Field Filter for Pivot Table" (code below)
Dim ws As Object
ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Activate()
ws.PivotTables(PivotTableName).PivotFields(Field).CurrentPage = Criteria

Input
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

The problem was that within the PivotTable Options, the "Refresh data when opening the file" (under tab Data) was unchecked, therefore I needed to trigger a Refresh (PivotTable.RefreshTable) before applying the filter...

Now, I'm trying to change this value from the code ... Tried with your code above (I assume this is what it does), but before calling the function, when trying to Open Workbook in order to read the worksheet name, I get an error when "Refresh data when opening the file" is checked (it's like a deadlock)....Not sure if I can change the value without opening the workbook somehow

Thanks Gopal!
Best,
Theo

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Wed, 02 Jun 2021 11:02:00 GMT

Hi Theodoros,

Are you getting error while opening workbook or while applying filter? I tried changing filter of sample excel with two pivot and it worked for me with "Refresh data when opening the file" checked. I added ws.PivotTables(PivotTableName).PivotCache.Refresh at the very end of the code in 'Add Criteria to Page Field Filter for Pivot Table' code to refresh table after the criteria change.

My steps

1. Create Instance
2. Open Workbook
3. Add Criteria to Page Field Filter for Pivot Table
4. Close All Instance (with save)

I would recommend you record a macro from the Developer tab of Excel to check if the field name and everything is all right.

Thanks,

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

RE: MS Excel Pivot Table Filtering

TheodorosPapaso — Wed, 02 Jun 2021 12:41:00 GMT

Hi Gopal,
Unfortunately I get the error much before the 'Add Criteria to Page Field Filter for Pivot Table'.
I follow the same order as yours. Error comes on Open Workbook: "HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER)".... When I uncheck manually the "Refresh data when opening the file" it works fine. However, the excel that I will process with my solution comes with the property checked. Therefore I'm trying to apply a code to uncheck that property before calling the Open Workbook (unsuccessfully so far)
Thanks again!

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Wed, 02 Jun 2021 12:53:00 GMT

Can you put it in recover and retry with a 30 sec sleep and check?

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

RE: MS Excel Pivot Table Filtering

TheodorosPapaso — Wed, 02 Jun 2021 16:06:00 GMT

That was a good idea but unfortunately even after 30 sec sleep it goes back to the same error every time I call Open Workbook

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Fri, 04 Jun 2021 12:52:00 GMT

Hi Theodoros,

If you are okay with adding libraries you can try this, I was working on creating a VBO with ClosedXML, I added an Update Pivot Criteria action which you can try.

OpenWorkBook ->Update Pivot Criteria->Close Workbook

Thanks,

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

RE: MS Excel Pivot Table Filtering

TheodorosPapaso — Mon, 12 Jul 2021 18:12:00 GMT

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

RE: MS Excel Pivot Table Filtering

GopalBhaire — Tue, 13 Jul 2021 05:48:00 GMT

NP, Glad you could make it work 👍

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