Reading and excel sheet via transformations

[SpiderFlight]

Perhaps one of you gurus out there can be of some assistance. I am trying to figure out a way to read in a spreadsheet in the transformations and to pull certain cells out and populate and database.
Any assistance would be greatly appreciated.

 

[PrgrmsAll]

This code snippet might be of some use (with modifications, of course). It can be placed inside an ActiveX object.
===
' 243 (Excel Named Range)
Option Explicit

Function Main()

Dim sActualLocationOfData
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim oPkg
Dim oConn

' Location, which sheet and in which cells is our Data.
' This will produce a string like Sheet1!R14C8:R11C43

sActualLocationOfData = "=Sheet" & CStr(DTSGlobalVariables("SheetNumber").Value) & _
"!" & DTSGlobalVariables("DataLocation").Value

' Create and set up the Excel File to Import
Set Excel_Application = CreateObject("Excel.Application")

' Open Excel Workbook
Set Excel_WorkBook = Excel_Application.Workbooks.Open(DTSGlobalVariables("FileLocation").Value)

' Get the Worksheet
Set Excel_WorkSheet = Excel_WorkBook.Worksheets(Cint(DTSGlobalVariables("SheetNumber").Value))

' Tell Excel where to get the data and add a named range to the workbook.
' The DTS pump is expecting a source table of "ImportTable" so we name our range that.
Excel_WorkBook.Names.Add "ImportTable", sActualLocationOfData


' Save the changes back to the workbook. If you fail to do this then you will get
' a message box asking you if you want to make changes to the Excel spreadsheet
Excel_WorkBook.Save

' Clean Up Excel Objects
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing


' Now set the Excel Filename on the Connection
Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections("Excel File")

oConn.DataSource = DTSGlobalVariables("FileLocation").Value

' Clean Up DTS objects
Set oConn = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function