Jump to content

Control Pivot Table update


Shastaman
 Share

Recommended Posts

Implementing pivot table in Excel. I can create the pivot table in AutoIt without problem. The problem is that I want to defer recalculating the pivot table until I have added all data fields (otherwise each update takes a long time). I'll do a single update at end. This isn't working through AutoIt.

The COM code below, waiting to update at the end, works fine in VBA as a macro in Excel but the ManualUpdate property has no effect in AutoIt.

The general steps are:

create a pivot cache

create a pivot table using the pivot cache

set manual update property to false to defer updating the file until I specify

add pivot fields

do update

Step 3 is having no effect so it is recalculating after each added pivot field.

$range = $Alabama_sheet.Range("A1:I28278")

$pivotCache = $Excel.ActiveWorkbook.PivotCaches.Create(1, $range)

$destrange = $pivotSheet.Range("A1")

$pivotTable = $pivotSheet.PivotTables.Add($pivotCache, $destrange)

; problem here, doesn't get set

$pivotTable.ManualUpdate = True

ConsoleWrite("manualupdate: " & $pivotTable.ManualUpdate & @CRLF) ; this shows -1

. ; set pivotfields here

.

; we want to do an update here after setting all the pivot fields

; $pivotTable.Update()

Thanks

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...