Building Excel pivot tables programmatically

Question

I've written a script that builds excel reports. These reports may include pivot tables. To refresh a pivot table on load, I added refreshOnLoad="1" parameter to pivotCacheDefinition1.xml file. It works good with only one minor thing that I do not like - when I open a report, excel asks me "do you want to replace the contents of the destination cells"? I press "ok" and it refreshes pivot tables. I do not like this pop-up window with this question, and I think it's obvious that if refreshOnLoad="1" is specified then contents should always be refreshed. So, my question is how to suppress this window from popping up?

EDIT

According to this thread:

Whenever Excel will find that the update of it will replace the content of any existing cell,it will warn you.It is important feature.

So, does it mean that Excel will always warn, when it sees that pivot table contents should be refreshed?

EDIT

It seems like my problem is similar to this one. However, I'm not using VB macros, I'm building my reports with some other programming language (which programmatically unzips xlsx file and deals with all those xml files inside).

EDIT

Well, I tried both Application.AlertBeforeOverwriting = False and Application.DisplayAlerts = False, but none of them works.

OldUgly · Accepted Answer · 2016-04-25 02:50:33Z

1

I haven't been able to reproduce the problem, so these are more suggestions than answers.

I think the commands you are trying are occurring too late - Excel needs to know "not to alert before overwriting" before it tries to refresh. If it refreshes on open, then you set the property, you have the wrong sequence of events.

Try:

Do NOT put refreshOnLoad="1" in your file.
Add the VBA routine Workbook_Open() to the workbook.
In the VBA routine, set the property Application.AlertBeforeOverwriting = False, then invoke the method PivotTable.Refresh.

This should resolve any "sequence of events" problems.

answered Apr 25, 2016 at 2:50

OldUgly

2,1393 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacobian Over a year ago

I only wish, I could use refreshOnLoad=1 and still resolve "sequence of events". I just do not like the idea to rely heavily on VBA routines

Collectives™ on Stack Overflow

Building Excel pivot tables programmatically

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related