I am attempting to create an excel pivot table programatically using the
Excel 12.0 Library. The code I am using is below and I have marked the point
where the error occurs with *******
this.excelApp = new Excel.ApplicationClass();
Excel.Workbook wb = this.excelApp.Workbooks.Add(missing);
Excel.PivotCache pivotCache =
this.excelApp.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
pivotCache.Connection = this.txtConnStr.Text;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = this.txtQuery.Text;
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
Excel.Worksheet sheet = (Excel.Worksheet)this.excelApp.ActiveSheet;
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);
******* ERROR HAPPENS ON NEXT LINE
Excel.PivotTable pivotTable = pivotTables.Add(pivotCache,
this.excelApp.ActiveCell, missing, missing, missing);
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
Excel.PivotField pageField =
(Excel.PivotField)pivotTable.PivotFields("Control");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
It appears that the problem may have to do with the second parameter which
as I understand is the location on the sheet to put the pivot table. I have
tried all variations of the Range object I could think of. Examples of this
I've seen online used Application.ActiveCell which is what I'm using above,
but I always get the same error. The only thing I've been able to find online
references making sure Excel security is set to allow VBA code to run, which
my excel version is. Please help!
|
Similar Threads
