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@hymans.co.uk> wrote in message
> news:1167902902.243513.70360@q40g2000cwq.googlegroups.com:
>
> > 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:
> >
> > > 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@hymans.co.uk> wrote in message
> > > news:1167824560.736663.137790@a3g2000cwd.googlegroups.com:
> > >
> > > > 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:
> > > > > 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@hymans.co.uk> wrote in message
> > > > > news:1166701273.056563.6410@f1g2000cwa.googlegroups.com:
> > > > >
> > > > > > 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(DTSGlobalVariables("FileLocation").Valu
> > > > > > e)
> > > > > >
> > > > > > ' 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