Updating Excel PivotTable Table from C #

Question

I am trying to update a pivot table in an Excel worksheet and get the following exception:

Item method in the PivotTables class failed

Here is the code:

pivotSheet.Activate();
Microsoft.Office.Interop.Excel.PivotTables pivotTables = 
        (Microsoft.Office.Interop.Excel.PivotTables)pivotSheet.PivotTables(missing);
int pivotTablesCount = pivotTables.Count;  
    if (pivotTablesCount > 0)
    {
        for (int i = 0; i <= pivotTablesCount; i++)
        {
            pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
        }
    }

Any idea?

Answer 1

Assuming indexing starts from scratch, you will overflow the collection with your loop.

Try:

for (int i = 0; i < pivotTablesCount; i++)

If this does not work, Excel will probably start indexing from 1 not to 0.

Try:

for (int i = 1; i <= pivotTablesCount; i++)

Answer 2

This will help you work.

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel.Workbook mWorkBook;
Microsoft.Office.Interop.Excel.Sheets mWorkSheets;

oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet pivotSheet in mWorkSheets)
{
    Microsoft.Office.Interop.Excel.PivotTables pivotTables = pivotSheet.PivotTables();
    int pivotTablesCount = pivotTables.Count;
    if (pivotTablesCount > 0)
    {
        for (int i = 1; i <= pivotTablesCount; i++)
        {
            pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
        }
    }
}