So I get a post this morning from my client saying everything looks great and they can't find any issues with the latest release. Happy moment, cigar time.
Well, at this point I am over on the project and am working on my own time. That's fine considering that there might be future business there. Ok, so they mention 2 minor items that are annoyances that would be perfect. No obligation to do them but I have a few minutes so I dive in.
5 hours later I have it working. What was the issue? Pivot Tables in Excel and setting their filter values. For those poor saps who have to deal with it in the future, here is some C# code to hopefully save you some time:
private static void HandlePivotTableChanges(string p)
{
FindProcessInstances();
Console.WriteLine("Starting Excel. Please be patient while it loads.");
string format = "'Product_Container_Release_Metrics_Workbook.xls'!Format_Workbook";
Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.DisplayAlerts = false;
Console.WriteLine("Opening workbook.");
Workbook wb = ExcelApp.Workbooks.Open(p, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
Console.WriteLine("Running macro.");
ExcelApp.RunMacro(format);
Worksheet ws = (Worksheet)wb.Worksheets["Pivot Tables"];
string pivotTable = "Forecast to Go";
PivotTable pt = (PivotTable)ws.PivotTables(pivotTable);
pt.ManualUpdate = true;
PivotField pf = (PivotField)pt.PivotFields("Release (A)");
PivotItems items = (PivotItems)pf.PivotItems(m);
foreach (PivotItem item in items)
{
if (item.Value != string.Empty) // <--- CRITICAL LINE. Filters must have at least 1 value selected. Exception otherwise.
((PivotItem) items.Item(item.Value)).Visible = false;
}
pt.ManualUpdate = false;
ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;
Console.WriteLine("Saving workbook.");
wb.Save();
wb.Close(false, m, m);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
Console.WriteLine("Closing Excel.");
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelApp);
}
private static void FindProcessInstances()
{
Console.WriteLine("Finding open processes.");
KillProcesses("Excel");
KillProcesses("PowerPnt");
}
private static void KillProcesses(string processName)
{
Process[] processes = Process.GetProcessesByName(processName);
if (processes.Length > 0)
{
foreach (var p in processes)
{
p.Kill();
}
}
}
public static void RunMacro(this Application app, string macro)
{
app.Run(macro,
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m
);
}
