Excel Automation From .NET - creating a new worksheet

Question

I am attempting what seems like a simple task: using C# to create a new Excel document containing new worksheets.

For some reason, I am getting a strange COM error (0x800A03EC)

Has anyone managed to get this to work? Does anyone have suggestions as to how to troubleshoot this?

I've isolated this into the minimum amount of code:

using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace ExcelAutomation
{
    public static class ExcelTests
    {
        public static void CreateWorksheet()
        {
            try
            {
                var app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = true;
                var workBooks = app.Workbooks;
                var newWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet existingWorksheet = (Worksheet)newWorkbook.Sheets[1];

                Worksheet workSheet = (Worksheet)newWorkbook.Sheets.Add
                        (
                            null, // before
                            existingWorksheet,
                            null, // 1,
                            null //XlSheetType.xlWorksheet
                        );
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                Trace.WriteLine(string.Format("Caught COMException. Message: \"{0}\"", ex.Message));  
            }
        }
    }
}

The output window now says:

Caught COMException. Message: "Exception from HRESULT: 0x800A03EC"

Answer 1

The mistake I'm making is to use null for optional values that I don't want to set.

Instead I should use System.Reflection.Missing.Value

        Worksheet workSheet = (Worksheet)newWorkbook.Sheets.Add
                (
                    existingWorksheet, // before
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value, // 1,
                    System.Reflection.Missing.Value //XlSheetType.xlWorksheet
                );