Excel - Pivot Table Pop Up

[azhar1234]

Hi Guys,
I have been facing issue with Excel Pop Up when i am trying to refresh pivot table.

The thing is, i have my raw data in sheet 1 and i have pivot table for that data's in sheet2. My requirement is after  pasting  some data 's in sheet 1. I need to refresh the pivot table in sheet2. But i am stucked with Pop up saying that "There is already data in pivot, DO you want to refresh it".

My refresh Action is not getting completed or its not throwing exception also. If it can able to throw exception or its completed, i can do UI automation and click yes to that pop up.
But the stage is active in refresh action itself.. How to achieve this or avoid??

Could u guys help me to avoid pop up or click yes to pop up.

Thanks in advance..

------------------------------
Mohamed Azharudeen
------------------------------

[Mukeshh_k]

Hi @Mohamed Azharudeen , You can Try creating a temporary macro to see the VBA Code - replicate that code in Blueprism Object or  you can use the MS Excel Properties to suppress the alerts -Fore more details on Microsoft Documentation - https://learn.microsoft.com/en-us/office/vba/api/excel.application.displayalerts

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[LakshmiNarayan3]

Hi

did you try as in the below link , if not try once and check if it helps in suppressing warning

https://www.excelhowto.com/macros/how-to-disable-theres-already-data-here-do-you-want-to-replace-it-warning/

Regards

------------------------------
Lakshmi Narayana
------------------------------

[Mukeshh_k]

Just to add more - You can activate the Sheet 1 first, then move on to activate the Sheet 2, Refresh whole workbook - For refreshing the whole workbook - Simply add an action and use code stage to write :

Dim wb as Object = GetWorkbook(handle,workbookname)
wb.RefreshAll



and then use the display alert set as false > Pivot refresh, you can enable the display alert post this activity.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[_Amlansahoo]

HI @Mohamed Azharudeen Just set Display alert to false. The code snippet I have added below.

------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------

[KristaPedroso]

Hi Mohamed, 
What i did was simply set the pivot in your sheet "refresh when opening the file" ("refresh data when opening the file" in Pivot Table Options tab DATA) and after you paste the data you close the file and open again and the pivot is refreshed. 

------------------------------
Krista Pedroso
Financial analyst
Zimmer Biomet
Europe/Amsterdam
------------------------------