Cannot Add Worksheet

May 6, 2013 at 9:25 AM

I hope someone can help me fix this. I run into a null reference exception every time I try to add a new worksheet. The exception is thrown at this specific line of code:

workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Sheets.Add();

This is for an Excel AddIn (VSTO) by the way. Below is the whole code block of the error:
        if (workBook == null)
            if (ThisAddIn._excelApplication.ActiveWorkbook == null)
                workBook = ThisAddIn._excelApplication.Workbooks.Add(Missing.Value);
                workBook = ThisAddIn._excelApplication.ActiveWorkbook;

            workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Sheets.Add();
            ReportThread = new System.Threading.Thread(new System.Threading.ThreadStart(GetReports));
        catch (Exception ex)
            catch { }

            if (Aborted)
                MessageBox.Show("Aborted on user request.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Aborted = false;

            string Options = "";
            Options += "\n Profile: " + cmbProfile.Text;
            Options += "\n Report Type: " + cmbReportType.Text;
            Options += "\n Time Segment: " + cmbTimeSegment.Text;
            Options += "\n Date Range: " + cmbDateRange.Text;
            MessageBox.Show("No Data Returned with following combination of options. " + Options + 
                Environment.NewLine + Environment.NewLine + ex.ToString(), 
                "", MessageBoxButtons.OK, MessageBoxIcon.Information);
The details of the exception are as follows:

System.NullReferenceException: Object reference not set to an instance of an object.
at NetOffice.Factory.CreateObjectFromComProxy(COMObject caller, Object comProxy)
at NetOffice.ExcelApi.Sheets.Add(Object before, Object after, Object count, Object type)

Any help would be very much appreciate.

Thanks and best regards,

Mark Ibanez
May 9, 2013 at 8:37 PM
this sounds strange. i'm wondering why this very simple scenario doesnt works.
its possible to send me your (simplified) project so i can debug the problem?

Oct 16, 2013 at 12:08 PM

Maybe this could be helpful: We ran into the same error when the template used was made with a version lower the one used during the automation.
The inner exception was (sorry german os when testing)
System.Reflection.TargetInvocationException: Ein Aufrufziel hat einen Ausnahmefehler verursacht. ---> System.Runtime.InteropServices.COMException: Die Blätter können von Excel nicht in die Zielarbeitsmappe eingefügt werden, da sie eine geringere Anzahl von Zeilen und Spalten enthält als die Quellarbeitsmappe. Zum Verschieben oder Kopieren der Daten in die Zielarbeitsmappe können Sie die Daten auswählen und dann mithilfe der Befehle 'Kopieren' und 'Einfügen' in die Blätter einer anderen Arbeitsmappe einfügen.
It seems the problem is within the difference of max columns allowed in different excel versions. In our case we were using a template made with office 2010 and on the client was office 2007 installed.
Try creating a new template with the same office version that you will be using when your code automates excel.