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.
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)
' 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
' Clean Up Excel Objects
Set Excel_WorkBook = Nothing
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
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.