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.

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