Unable to set active Excel sheet C#

Question

I am running some C# code as part of a script component running in my SSIS package. I am trying to open an Excel file and change the name of the sheet prior to importing the file in the next step of my SSIS package. I am getting an error on the line where I am trying to initialize "oSheet".

The error specifies: "Error 1 One or more types required to compile a dynamic expression cannot be found. Are you missing a reference? C:\Temp\Vsta\SSIS_ST110\VstaTP9LtckEMUWOXYp4Zy3YpQ\Vstau3xOw__Ey1kaOxXFoq0ff8g\ScriptMain.cs 107 26 ST_005c649f34584ed6873a7fde862ab2c9 "

I've not used C# for a while and was hoping someone could point me in the right direction. Thanks in advance!

Code:

        public void Main()
    {
        String s = (String)Dts.Variables["FilePath"].Value;
        String FileName = s.Substring(45,s.Length - 45); //45 = hardcoded value for known index of the start of the file name
        MessageBox.Show(FileName);
        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;

        try
        {
            oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.Visible = false;
            oWB = (Excel.Workbook)oXL.Workbooks.Open(s);
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            //oSheet = (Excel._Worksheet)oXL.ActiveSheet;
            //oSheet = (Excel._Worksheet)oWB.Worksheets.Item(0);
            //oSheet = (Excel._Worksheet)oXL.Worksheets[FileName];
            oSheet.Name = "NLTWNH";
            oWB.Close(s);

        }
        catch (Exception ex)
        {
            //do nothing
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }      

Answer 1

I was missing a reference to "Microsoft.CSharp.dll" in my SSIS script task. To add the reference in Visual Studio 2012 click Project, Add Reference, then in the Framework tab scroll to find Miscrosoft.CSharp, check the corresponding box, and click OK.

Answer 2

First, add a reference to the Microsoft Excel Interop DLL. You do this by right clicking the References folder in the Solution Explorer. Then click Add Reference.

Click on the COM tab in the "Add Reference" window, and scroll down to your version of Excel's Object Library (I have chosen 15, but you may chose another version). Then click OK.

Now, it looks like your using statement should do something like this:

using Excel = Microsoft.Office.Interop.Excel;

Also, note that your oXL constructor can now just be

oXL = new Excel.Application();