Need Help with Excel Worksheet Object in Script Task

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  September 3, 2015 at 12:17 pm

  #304040

  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.

  CSDunn

• //Ravi

  SSCrazy

  Points: 2612

  More actions

  September 4, 2015 at 9:21 am

  #1824712

  Looks like your Variable is data type is object. Try DT_STR and see if you get the same error.

  ------------
  🙂