On the newsgroups recently there has been a great deal of interest in importing data from Microsoft Excel into SQL Server using DTS. One of the questions that caught my eye was "How do I import values from cells from anywhere on the page?". You can do this using Named Ranges in the Excel spreadsheet.

The package contains three global variables. The FileLocation is where the Excel File will be located. The SheetNumber is the sheet on which we find the data. The DataLocation is how we create a Named Range.

Name	Type	Value
FileLocation	String	F:\Workings\ExcelNamedRangeSample.xls
SheetNumber	String	1
DataLocation	String	R14C8:R43C11

The data location R14C8:R43C11 refers to the range Row 14 Column 8 to Row 43 column 11. For the first set of co-ordinates we need to specify the cell immediately above the top left hand corner of our data.

Below is the ActiveX Script commented to explain the process.

' 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

A fully working sample package is available for download, which should illustrate this method better than any description. The package has a password of "sqldts" to ensure it is compatible across all versions of SQL Server and all service packs levels. Once you have opened the package you will need to amend the Excel connection to point it at the sample file on your system. Do not elect to make changes to the transformation task when asked.