Creating an Excel Pivot Table using COM in C# producing 0x800A03EC

M

MattW

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top