topic RE: Excel - Pivot Table Pop Up in Product Forum

Excel - Pivot Table Pop Up

azhar1234 — Mon, 01 May 2023 09:09:00 GMT

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

RE: Excel - Pivot Table Pop Up

Mukeshh_k — Mon, 01 May 2023 10:07:00 GMT

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

RE: Excel - Pivot Table Pop Up

plnarayana777 — Mon, 01 May 2023 10:08:00 GMT

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

RE: Excel - Pivot Table Pop Up

Mukeshh_k — Mon, 01 May 2023 10:25:00 GMT

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.

RE: Excel - Pivot Table Pop Up

_Amlansahoo — Tue, 02 May 2023 10:34:00 GMT

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

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

RE: Excel - Pivot Table Pop Up

KristaPedroso — Tue, 02 May 2023 14:35:00 GMT

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