none
Need Help with Excel Worksheet Object in Script Task RRS feed

  • Question

  • I am trying to create a script task that will rename the first Excel workbook worksheet. The script takes in the name of the workbook via a package variable named 'SourceFile'. 

    I'm getting a compile error in the code here:

    Worksheet = Workbook.Worksheets[1];

    Msg: 

    Error 1 Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet'. An explicit conversion exists (are you missing a cast?) C:\Users\cdunn\AppData\Local\Temp\SSIS\ST_c30de67841434972b96a1c9744cfddc9\ScriptMain.cs 43 25 ST_c30de67841434972b96a1c9744cfddc9

    How do I perform the conversion? The following is the code I am working with:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ST_c30de67841434972b96a1c9744cfddc9.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
    
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
    
    
            public void Main()
            {
                string sFileName;
                sFileName = Dts.Variables["User::SourceFile"].Value.ToString();
                Excel.Application ExcelApplication; new Excel.Application();
                Excel.Workbook Workbook;
                Excel.Worksheet Worksheet;
    
                Workbook = ExcelApplication.Workbooks.Open(sFileName, //OPEN requires all of these arguments.
                    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);
    
                Worksheet = Workbook.Worksheets[1];
                Worksheet.Name = "NewWorksheetName";
    
                Workbook.Save();
                ExcelApplication.Application.Workbooks.Close();
                Worksheet = null;
                Workbook = null;
    
                ExcelApplication.Quit();
    
    
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }
    }

    Thank you for your help.

    cdun2

    • Moved by ArthurZ Thursday, September 3, 2015 6:01 PM Does not appear SSIS related
    Thursday, September 3, 2015 4:21 PM

Answers

  • Hi

    Try rewriting

    Excel.Application ExcelApplication; new Excel.Application();

    AS

    Excel.Application ExcelApplication= new Excel.Application();

    This should fix above issue.

    ----------------------------------------------------------------------------------------------------------------------------------------

    Please Mark as Answer if you find it helpful.

    Monday, September 7, 2015 5:20 AM

All replies

  • Hi

    Try Casting as follows:
    Worksheet = (Excel.Worksheet)Workbook.Worksheets[1];
    Friday, September 4, 2015 9:54 AM
  • Hi

    Try Casting as follows:
    Worksheet = (Excel.Worksheet)Workbook.Worksheets[1];

    Thanks.

    That typecast seems to work, but now the assignment to 'Workbook' is not correct:

    Error 1 Use of unassigned local variable 'ExcelApplication' C:\Users\cdunn\AppData\Local\Temp\SSIS\520cf795f2854aa39e5d65cb2f56c20b\ScriptMain.cs 32 24 st_c30de67841434972b96a1c9744cfddc9

                string sFileName;
                sFileName = Dts.Variables["User::SourceFile"].Value.ToString();
                Excel.Application ExcelApplication; new Excel.Application();
                Excel.Workbook Workbook;
                Excel.Worksheet Worksheet;

                Workbook = ExcelApplication.Application.Workbooks.Open(sFileName, //OPEN requires all of these arguments.
                    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);

    Friday, September 4, 2015 3:30 PM
  • Hi

    Try rewriting

    Excel.Application ExcelApplication; new Excel.Application();

    AS

    Excel.Application ExcelApplication= new Excel.Application();

    This should fix above issue.

    ----------------------------------------------------------------------------------------------------------------------------------------

    Please Mark as Answer if you find it helpful.

    Monday, September 7, 2015 5:20 AM