c# - Why does this attempt to create a PivotTable (Excel Interop) fail?

Question

Based on code I found here, I'm trying to create a Pivot Table like so:

var range = _xlSheet.Range[_xlSheet.Cells[6, 1],
                           _xlSheet.Cells[6, _grandTotalsColumn]];
ListObject tbl = WriteToExcelTable(_xlSheet, "tbl", "A6");
var pch = _xlBook.PivotCaches();

pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month!A6:F94")
    .CreatePivotTable(tbl, "PivTab1", Type.Missing, Type.Missing);
. . .

public ListObject WriteToExcelTable(Worksheet WSheet, string TableName, string CellStr = "A1", bool ClearSheetContent = false)
{
    Range range;

    if (ClearSheetContent)
        WSheet.Cells.ClearContents();  // clear sheet content

    // get upper left corner of range defined by CellStr
    range = (Range)WSheet.get_Range(CellStr).Cells[1, 1];   

    // Write table to range [ - don't have the helperfunc code commented this out ]
    //HelperFunc.WriteTableToExcelSheet(WSheet, this._tbl, range.Address);

    // derive range for table, +1 row for table header
    // [doesn't compile, so commented out] rang
    //range.get_Resize(this.RowCount + 1, this.ColumnCount);
    range = range.get_Resize(102, 5); // faking it to see what happens

    // add ListObject to sheet
    ListObject tbl = WSheet.ListObjects.AddEx(
        XlListObjectSourceType.xlSrcRange,
        range,
        XlListObjectHasHeaders: XlYesNoGuess.xlYes);

    // set name of excel table
    tbl.Name = TableName;

    // return excel table (ListObject)
    return tbl;
}

...but it crashes on this line:

pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month!A6:F94")
                .CreatePivotTable(tbl, "PivTab1", Type.Missing,   
Type.Missing);

...with:

System.ArgumentException was caught
  HResult=-2147024809
  Message=The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
  Source=""
  StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.PivotCache.CreatePivotTable(Object TableDestination, Object TableName, Object ReadData, Object DefaultVersion)
       at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 3312
       at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 180
  InnerException:

What is wrong with my approach? Note that I had to comment out two lines from the borrowed method (WriteToExcelTable()) because they wouldn't compile for me.

UPDATE

I also tried this alternative:

private void AddPivotTable()
{
    int rowIdx = 42; // try 42 for now; adjust as/if necessary
    var pch = _xlBook.PivotCaches();
    pch.Add(XlPivotTableSourceType.xlDatabase, "PivotData!A6:D" + rowIdx)
        .CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);
    . . .

...which crashed on the last line shown with:

System.Runtime.InteropServices.COMException was caught
  HelpLink=C:Program Files (x86)Microsoft OfficeOffice121033XLMAIN11.CHM
  HResult=-2146827284
  Message=Cannot open PivotTable source file 'C:UserscshannonDocumentsPivotData'.
  Source=Microsoft Office Excel
  ErrorCode=-2146827284
  StackTrace:
       at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
       at CallSite.Target(Closure , CallSite , ComObject , Object , String , Object , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
       at CallSite.Target(Closure , CallSite , PivotCache , Object , String , Object , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid5[T0,T1,T2,T3,T4](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
       at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 3172
       at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 180
  InnerException:

UPDATE 2

Even after removing the "PivotData!", which apparently refers to a file named that from which to read data, so that the line is now:

pch.Add(XlPivotTableSourceType.xlDatabase, "A6:D" + rowIdx)

...it still crashes, now with:

System.Runtime.InteropServices.COMException was caught
  HelpLink=C:Program Files (x86)Microsoft OfficeOffice121033XLMAIN11.CHM
  HResult=-2146827284
  Message=Cannot change part of a merged cell.
  Source=Microsoft Office Excel
  ErrorCode=-2146827284
  StackTrace:
       at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
       at CallSite.Target(Closure , CallSite , ComObject , Object , String , Object , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
       at CallSite.Target(Closure , CallSite , PivotCache , Object , String , Object , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid5[T0,T1,T2,T3,T4](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
       at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 3174
       at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:ProjectsReportRunnerReportRunnerProduceUsageProduceUsageRpt.cs:line 180
  InnerException:

UPDATE 3

Because the exception refers to problems with merged cells, I added this:

var pivotData = _xlSheet.Range["A6:D42"];
pivotData.UnMerge();

...but it still fails (now with "PivotFields method of PivotTable class failed Exception Source: Microsoft Office Excel Exception StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)").

NOTE: I get "Do you want to replace the contents of the destination cells in [Book3]Produce Usage by Month?" (twice) before it fails

Answer 1

Without seeing the specifics of your workbook, it's hard to diagnose some of the issues you list. The basic means of generating a pivot table would be this:

Range sourceData = _xlBook.Worksheets["Produce Usage by Month"].Range["A6:F94"];

PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
PivotTable pvt = pc.CreatePivotTable(sheet2.Range["A1"], "SummaryPivot");

pvt.PivotFields("Col1").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Col2").Orientation = XlPivotFieldOrientation.xlColumnField;
pvt.AddDataField(pvt.PivotFields("Col3"), "Sum of Col3", XlConsolidationFunction.xlSum);

It looks like your code is not being really explicit about some of the objects. For example Produce Usage by Month!A6:F94 means something to Excel (and is probably interpreted by VBA), but I think the interop libraries need more explicit declaration of objects. In strong typed, this would be what I listed for the sourceData object. sheet2 is therefore an Excel.Worksheet object.

Regarding some of the warnings, such as overwrite warnings, if you change the DisplayAlerts property of the Excel application object to false, it will take care of many of these.