Why would I get, "InteropServices.COMException / ForwardCallToInvokeMember" with this code?

Question

After retrieving data and storing it in a generic list, I've got this code to populate an Excel spreadsheet using Excel Interop code:

. . .
InitializeExcelObjects();
_currentTopRow = DATA_STARTING_ROW;
foreach (PriceVarianceData _pvd in _pvdList)
{
    AddData(_pvd);
    _currentTopRow = _currentTopRow + 1;
}
. . .

private void AddData(PriceVarianceData _pvd)
{
    var UnitCell = (Excel.Range)_xlSheet.Cells[_currentTopRow, 0];
    UnitCell.Value2 = _pvd.Unit;
    var ShortNameCell = (Excel.Range)_xlSheet.Cells[_currentTopRow, 1];
    ShortNameCell.Value2 = _pvd.ShortName;
    . . .
}

It crashes on the first line of the AddData() method (the attempted assignment to UnitCell), with "System.Runtime.InteropServices.COMException was unhandled... StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object..."

I don't know why this would be; I'm calling this prior to trying to add data:

private void InitializeExcelObjects()
{
    _xlApp = new Excel.Application
    {
        SheetsInNewWorkbook = 1,
        StandardFont = "Calibri",
        StandardFontSize = 11
    };
    Thread.Sleep(2000);
    //var apartmentSt8 = Thread.CurrentThread.GetApartmentState(); <= STA, as it should be

    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlSheets = _xlBook.Worksheets;
    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
}

The full exception is:

System.Runtime.InteropServices.COMException was unhandled HResult=-2146827284 Message=Exception from HRESULT: 0x800A03EC Source="" ErrorCode=-2146827284 StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.Range.get__Default(Object RowIndex, Object ColumnIndex) at Pivotal.FormMain.AddData(PriceVarianceData _pvd) in c:\Projects\Pivotal\Pivotal\Form1.cs:line 155 at Pivotal.FormMain.GenerateAndSaveSpreadsheetFile() in c:\Projects\Pivotal\Pivotal\Form1.cs:line 134 at Pivotal.FormMain.buttonRun_Click(Object sender, EventArgs e) in c:\Projects\Pivotal\Pivotal\Form1.cs:line 77 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at Pivotal.Program.Main() in c:\Projects\Pivotal\Pivotal\Program.cs:line 19 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

What could be the issue here? I looked at this, but none of the suggestions seemed to apply to this situation.

UPDATE

I noticed this morning that I had about 40 Excel processes running, so wondered if that might be the problem (they're not getting shut down/disposed, I guess) and killed them all. I still had the same result, though - and that Excel process also hung around (as could be expected, what with the sudden cessation of execution).

Answer 1

In the first line of your AddData() method you are trying to access the Cells with a 0-based index, but the column- and row-indexes in excel are all 1-based indexes (in fact many indexes in Excel are 1-based - but not all).

To fix this you'll have to do 2 things in your case:

1. Ensure that _currentTopRow is a 1-based index (i.e. minimum valid value is 1)

2. Change your AddData() method as follows:

   private void AddData(PriceVarianceData _pvd)
   {
       // Note changed column index (from 0 to 1)
       var UnitCell = (Excel.Range)_xlSheet.Cells[_currentTopRow, 1];
       UnitCell.Value2 = _pvd.Unit;
   
       // Note changed column index (from 1 to 2)
       var ShortNameCell = (Excel.Range)_xlSheet.Cells[_currentTopRow, 2];
   
       ShortNameCell.Value2 = _pvd.ShortName;
       . . .
   }