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....
No comments:
Post a Comment