CODEWITHSUNDEEP
excelpivot-table
Jacobian
Jacobian

Reputation: 10812

Building Excel pivot tables programmatically

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.

Upvotes: 2

Views: 236

Answers (1)

OldUgly
OldUgly

Reputation: 2119

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:

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

This should resolve any "sequence of events" problems.

Upvotes: 1

Related Questions