Jump to content
Sign in to follow this  
Shastaman

Control Pivot Table update

Recommended Posts

Shastaman

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

Share this post


Link to post
Share on other sites
roach4411

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

Thanks

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.