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
