Hello

I am importing data from an Excel spreadsheet into a table, via DTS. I
am trying to use a named range in the spreadsheet, and have used a
script from Allan Mitchell to achieve this:-

' 243 (Excel Named Range)
Option Explicit

Function Main()

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

' 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(DTSGlobalVariable s("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

The named range is created successfully, and saved to the Excel file.
My problem is that the Transform Data Task is not picking up the named
range, and so does not make it available in the dropdown list for
Table/View on the Source tab. I added some extra code, also from
Allan, as follows:-

Set oPkg = DTSGlobalVariables.Parent
Set cus= oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
cus.SourceObjectName = "ImportTable"

however, this through an error on the Transform Data Task, saying that
it couldn't find 'ImportTable'.

Is there something obvious that I'm doing wrong? Like I say, it is
creating the named range ok, it's just that the data pump task is
refusing to acknowledge its presence. My global variables for file
location, data, etc., are also correct, as far as I can see.

Any help would be appreciated

Thanks

Denise

OK so with the Named Range created if you drop on an Excel Connection to
designer. and try to move data from it to somewhere else then the named
range does not appear?



--


Regards



Allan Mitchell


Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com


"dcrawley" <denise.crawley (AT) hymans (DOT) co.uk> wrote

Hello Allan

Apologies for not getting back sooner. I don't have a pc at home and I
have just come back after the Christmas break.

Yes, that's correct. the Excel connection just doesn't see the named
range. Do you have an idea?

Thanks

Denise



Allan Mitchell wrote:

A named range should be seen as a table. What if you use that Excel
file in a brand new package?

--


Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com




"dcrawley" <denise.crawley (AT) hymans (DOT) co.uk> wrote

If I use the Excel file in a brand new package the named range is not
seen by that one either. I checked the file beforehand, and the named
range is definitely saved within the file, but it just doesn't seem to
be visible within DTS.

There is another named range that was saved into the Excel file before
it was given to me. This named range is available in the dropdown
list, so it's not like the DTS packages aren't picking them up at all.
There just seems to be a problem with the one that's created 'on the
fly'.

Thanks for taking a look at this, Allan. I appreciate it.


Allan Mitchell wrote:

Can you send me the file?

--


Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com




"dcrawley" <denise.crawley (AT) hymans (DOT) co.uk> wrote

I'm afraid not, as it's a company file. Is there any other information
I can give you without sending the file?

Sorry, I know this makes it more difficult.

Allan Mitchell wrote: