Get excel sheet names in a script taks

Hi,

I had seen a question asking about getting all the excel sheet names in a work book, so I've created the attached example package.

Basically I'll loop over a specific folder, get all the excel file names into a variable, pass it to a script task to get me the worksheet names.





In the SCRIPT TASK, add a reference to  MICROSOFT.OFFICE.INTEROP.EXCEL

Then add this code block..

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing  'var dispenser
        Dts.VariableDispenser.LockForRead("User::StrExcelFilePath")
        Dts.VariableDispenser.LockForWrite("User::StrExcelSheetName")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim totalWorkSheets As Excel.Worksheet
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""
        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(Vars("User::StrExcelFilePath").Value)
        ' this code gets the names off all the worksheets
        For Each totalWorkSheets In objWorkBook.Worksheets
            'ExcelSheetName += totalWorkSheets.Name
            Vars("User::StrExcelSheetName").Value = totalWorkSheets.Name.ToString
            MsgBox(totalWorkSheets.Name.ToString)
        Next totalWorkSheets

        Dts.TaskResult = ScriptResults.Success
        Vars.Unlock()
    End Sub
End Class

Hope that helps someone....