Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 1147
Product
ExcelWriter
Title
Problems Autofitting PivotTables
Problem

The ExcelApplication object does not provide a way to Autofit PivotTables. This must be done through a VBA script in the Excel Workbook, usually by running autofit in the Workbook_Open() event.


Private Sub Workbook_Open()
   For Each ws In Me.Worksheets
       ws.Cells.Columns.AutoFit
       ws.Cells.Rows.AutoFit
   Next
End Sub

When using PivotTables with large amounts of data sometimes events do not get fired in the order they are expected to. Workbook_Open() may occur before the PivotTable data actually gets populated with data and this can result in autofit not having any effect.

Solution

The solution is to disable the “Refresh on Open” Option in the Pivot Table Options and instead explicitly refresh the PivotTable data BEFORE autofitting the worksheet.


Private Sub Workbook_Open()
   Worksheets(1).PivotTables("OrderPivot").PivotCache.Refresh

   For Each ws In Me.Worksheets
       ws.Cells.Columns.AutoFit
       ws.Cells.Rows.AutoFit
   Next
End Sub
Related Links
OfficeWriter Home Page
OfficeWriter Enterprise Edition
Latest OfficeWriter News
OfficeWriter: Programmatic Runtime Control

Created : 8/8/2006 6:21:55 PM (last modified : 8/8/2006 6:21:55 PM)
Rate this article!
Comments