Listing and refreshing all Pivot tables

Question

I'm trying to create a script that refresh all data in a worksheet and then refreshes the pivot tables afterward (because data in pivot tables are normally refreshed before the data from Databases the result is not correct by default).

To do this i use this script (because I'm starting this script automatically each day at 9.00).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            // Get fully qualified path for xlsx file
            var spreadsheetLocation = "C:\\update_rapport\\Salgsrapport.xlsx";

            var exApp = new Microsoft.Office.Interop.Excel.Application();
            var exWbk = exApp.Workbooks.Open(spreadsheetLocation);
            //var exWks = (Microsoft.Office.Interop.Excel.Worksheet)exWbk.Sheets["responses(7)"];

            exWbk.RefreshAll();

            exApp.DisplayAlerts = false;

            // This part is not correct. Need to find all pivot tables and update them
            Object PivotTables(
                Object Index
            );


            string save_file_name = "C:\\temp\\updated\\Salgsrapport.xlsx";
            exWbk.SaveAs(save_file_name);
            exWbk.Close(true);
            exApp.Quit();

        }

    }
}

The closest thing i have found to looping through all pivot tables is this: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.pivottables.aspx

However it's not a complete example, and I have never really programmed C# before so I'm kind of lost here. There might be a simpler solution to this problem, any hint is appreciated.

Answer 1

Since it's a year later, I'm guessing you've already found a solution to your problem... that said, for the benefit of future generations, I was also seeking a solution for a similar problem.

As far as I can tell, the PivotCaches method at the workbook level appears to expose all Pivot Table objects. I am still working through my issue, but if your goal is to refresh all pivot tables, then something like this should work:

foreach (Microsoft.Office.Interop.Excel.PivotCache pt in exWbk.PivotCaches())
    pt.Refresh();

I do know for sure that if you know the name of your pivot table, you can do this:

exWbk.Sheets["Sheet1"].PivotTables["PivotTable1"].PivotCache.Refresh();

I have been using this successfully for a while.

One thing that does confuse me about your question, however, is that I was under the impression that exWbk.RefreshAll(); refreshes every object in the workbook and takes dependencies into account. I was surprised to hear that invoking that did not also update the pivot tables.

Update:

I found a better solution here. This is what I was looking for to begin with.

http://www.pcreview.co.uk/threads/vsto-how-to-find-all-pivot-tables-in-the-workbook.3476010/

Excel.PivotTables pivotTables1 =
   (Excel.PivotTables)ws.PivotTables(Type.Missing);

if (pivotTables1.Count > 0)
{
    for (int j = 1; j <= pivotTables1.Count; j++)
}