Shastaman Posted June 18, 2008 Share Posted June 18, 2008 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 More sharing options...
roach4411 Posted June 19, 2008 Share Posted June 19, 2008 I'm interested in this as well. Does anyone have any experience with using COM to defer the PivotTable Update? Thanks Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now