Wednesday, March 17, 2010

Excel Pivot Tables and Filtering

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
);
}

Wednesday, March 03, 2010

SaviDataSet 1.0.0.5 Released

I just posted the latest version of the SaviDataSet reader/writer. The latest version allows for its use from the command line for converting SAS datasets to delimited, XML, and Excel files (2003 & 2007).

Please see the Help File in the Savian--> SaviDataSet --> Help.txt folder for instructions.

I will be incorporating a write feature in at some point but it is more complex since variables need to be defined.

The latest release can be found here:

SaviDataSet 1.0.0.5

SAS throwing RPC error

If you are doing code in C#  and get this error when creating a LanguageService: The RPC server is unavailable. (Exception from HRESULT:...