Jump to content
Sign in to follow this  

Control Pivot Table update

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


Share this post

Link to post
Share on other sites

I'm interested in this as well. Does anyone have any experience with using COM to defer the PivotTable Update?


Share this post

Link to post
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
Sign in to follow this