I felt confused about the variables used in this article http://www.sqldts.com/default.aspx?243

1) ' 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

What does "ImportTable" mean? Should I change it?

2)' Now set the Excel Filename on the Connection
    Set oPkg = DTSGlobalVariables.Parent
    Set oConn = oPkg.Connections("Excel File")

What does "Excel File" mean?

3) ¡®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¡¯
I always received this message, but can¡¯t figure out why?




Can anyone explain it to me? Thanks!!!


flyfish

__________________
flyfish

"ImportTable" is a name used by the author of the article, Allan Mitchell, to create named Range in Excel, he could just as easily have used xclNR or any other name. He uses it in two places 1) in the VB Script to create the named range and then in the data pump task to extract it from Excel.

Without the named range the pump task would import the whole sheet.

"Excel File" is the name of the Connection object that connects to the Excel file

By adding the named range you are saving the excel file

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

By adding the named range you are modifying the excel file -- consequently it should be saved and closed, otherwise the named range won't be in the excel file when you try and access it

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thank you for your response to my post!
Currently I am working on a project to load an Excel file into the database. Each file has 12 to 15 sheets which have different names and changed each month. I only need to load the data from one sheet.
In my prpject, I tried to create a DTS package to finish this auto-loading process. But I am wondering how can I pass the global variables from ActiveX script to the Excel to setup the sheet name.
In my ActiveX script code, I use two global variables: one for the Excel file location, another one for the sheet name which I can select from local table in the ActiveX script. The following was the code I used in my DTS package.

Dim sActualLocationOfData
    Dim Excel_Application
    Dim Excel_WorkBook
    Dim Excel_WorkSheet
    Dim oPkg
    Dim oConn
    Dim tsk

    dim conn
    dim myRecordset
    dim iRowCount


       set DTSGlobalVariables("MyConn").value = CreateObject("ADODB.Connection")
    set myRecordset = CreateObject("ADODB.Recordset")

       set conn = DTSGlobalVariables("MyConn").value
       conn.provider="sqloledb"
        conn.open "local", "username", "password"
     conn.DefaultDatabase = "databasename"


     SQLCmdText = "Select excel_sheetname from acct_excel"

     myRecordset.Open SQLCmdText, Conn

     msgbox(myRecordset.Fields("Excel_sheetName").value )

     DTSGlobalVariables("SheetName").value = myRecordset.Fields("Excel_sheetName")



    ' Location, which sheet and in which cells is our Data.
    ' This will produce a string like Sheet1!R14C8:R11C43

    sActualLocationOfData = "="& DTSGlobalVariables("SheetName").Value & "!"&DTSGlobalVariables("DataLocation").Value

    Msgbox(sActualLocationOfData)



    ' 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.Name.(DTSGlobalVariables ("SheetName").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
(Here is my problem?)

    ' 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 ( I received this erroe message too)

    ' 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 tsk = oPkg.tasks("ActiveX Script task").CustomTask
    Set oConn = oPkg.Connections(1) (my problem ?, too)

    oConn.DataSource = DTSGlobalVariables("FileLocation").Value

    ' Clean Up DTS objects
    Set oConn = Nothing
    Set oPkg = Nothing

    Main = DTSTaskExecResult_Success




flyfish

Two approaches -- modify the steps your self in the ActiveX Script task or setup the Global Variables in the ActiveXScript task and then follow that with a Dynamic Properties Task.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

DTSGlobalVariables("SheetName").value = myRecordset.Fields("Excel_sheetName")
 sActualLocationOfData = "="& DTSGlobalVariables("SheetName").Value & "!"&DTSGlobalVariables("DataLocation").Value

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(DTSGlobalVariables("Shee tName").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

By using the code above, the data in the "ImportTable" still have all the sheets instead the the sheet specified by the varivable "sActualLocationOfData". Did anybody know why? Thanks in advance?

flyfish