C# Excel Worksheet Object reference not set to an instance of an object

Question

When I run a SSIS package Interactively I am getting the error Object reference not set to an instance of an object on a Script task during run time.

So I debugged the code by using break points and after going step by step the code fails at this line objExcelWbk.Close(true, Type.Missing, Type.Missing);

I have checked all my references they are all there and the imports are fine too.

The function is provided below

public void FormatExcel_DVP(string strFinalFileName, string ExcelOutputFolder, SqlConnection Conn)
{
    Microsoft.Office.Interop.Excel.ApplicationClass objExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbook objExcelWbk = default(Excel.Workbook);
    Microsoft.Office.Interop.Excel.Worksheet objWrksheet = default(Excel.Worksheet);
    string sFilePath = string.Empty;
    DataSet ds = new DataSet();
    string DVP_Name = string.Empty;
    string sFilename = string.Empty;

    int RowCount = 0;
    try
    {
        SqlCommand cmd = new SqlCommand("select distinct LTRIM(RTRIM(DVP_Name))as DVP_Name from SBBCP_DVP_SVP Order By DVP_Name", Conn);
        SqlDataAdapter _da = new SqlDataAdapter(cmd);
        _da.Fill(ds);


        if (ds.Tables[0].Rows.Count > 0)
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DVP_Name = dr[0].ToString().Trim().Replace("'", "''");
                sFilename = DVP_Name.Trim();

                sFilePath = strFinalFileName + ExcelOutputFolder.Trim() + sFilename;

                if (System.IO.File.Exists(sFilePath))
                {
                    objExcelWbk = objExcelApp.Workbooks.Open(sFilePath.Trim(), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    objExcelApp.DisplayAlerts = false;
                    objExcelApp.Visible = false;

                    objWrksheet = (Excel.Worksheet)objExcelWbk.Worksheets["Details"];
                    ((Microsoft.Office.Interop.Excel._Worksheet)objWrksheet).Activate();

                    Microsoft.Office.Interop.Excel.Range range;

                    range = (Excel.Range)objWrksheet.get_Range("A1:J1", Type.Missing);

                    range.Interior.ColorIndex = 15;
                    range.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
                    range.NumberFormat = "@";
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                    range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    range.WrapText = true;
                    range.Font.Bold = true;
                    range.Font.Name = "Arial";
                    range.Font.Size = 10;
                    range.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);


                    RowCount = objWrksheet.UsedRange.Rows.Count;
                    range = objWrksheet.get_Range("A2:J2", "A" + RowCount + ":J" + RowCount);
                    range.WrapText = true;


                    FormatPivotTable(ref objExcelWbk, "Summary");
                    objExcelWbk.SaveAs(sFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
        }
        objExcelWbk.Close(true, Type.Missing, Type.Missing);
        objExcelApp.Quit();
    }
    catch (Exception e)
    {
        throw e;
    }
}

ObjExcelWbk is opened per data row, and only if the file is open - however it is closed long since outside that scope.. So yes, Id expect there to be times when it fails. — BugFinder
– BugFinder, Commented Jun 7, 2016 at 13:52
@BugFinder you are right when I debug it does not go in the if statement. So you mean to put it inside if statement but out side for each loop yes? — user4410715
– user4410715, Commented Jun 7, 2016 at 14:02
Put it in the same code block as it is created/instantiated in. In your case, the if(file exists). — Tab Alleman
– Tab Alleman, Commented Jun 7, 2016 at 14:03
@BugFinder it works thank you. If you can put an answer i would like to mark it as correct and +1. — user4410715
– user4410715, Commented Jun 7, 2016 at 14:10

BugFinder · Accepted Answer · 2016-06-07 14:18:59Z

1

In your code you have

for each row
{
  if file exists 
  {
     open spreadsheet.
     do stuff
  }
}
Close spreadsheet

the problem is that could be senarios where your spreadsheet was never opened and its trying to close it but it hadnt been set.

You should close the spreadsheet in the same scope you opened it, so in my pseudo code, after the "do stuff" - for then, if you opened it as it didnt barf or die, you have something you can close.

answered Jun 7, 2016 at 14:18

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BugFinder Over a year ago

The other option which might make your code more efficient would be to move the opening of the spreadsheet to the before each row if you're 99% likely to always have 1 or more things to do - as it looks like you open the same spreadsheet for each iteration.

Collectives™ on Stack Overflow

C# Excel Worksheet Object reference not set to an instance of an object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related