Post reply

Get the name of an excel sheet dynamically in dts

  • November 7, 2008 at 12:55 pm

    #72093

    I've got an excel file that's sent to us with a changing file name and a changing sheet name. I can handle the pattern matching for the file name in an activex task, but I can't quite figure out how to check the names of the sheet(s) in the workbook to find the one that matches the pattern I'm looking for and set the global variable to it so that I can use it later in the package.

    Ex.

    File name today: clientdata_342395.xls

    sheet name today: clientdata_4333243453

    file name next week: clientdata_43432134214.xls

    sheet name next week: clientdata_34134143434312

    Any help would be appreciated.

  • November 10, 2008 at 2:08 am

    #896159

    Hi,

    Check out the attachment, it's a SP that I've built to get all the sheets' names from an Excel workbook.

    HTH,


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • November 11, 2008 at 2:07 am

    #896802

    HI

    Hopefully you are aware of the problems with format when you import data from Excel

    with DTS or SSIS. I would do the pattern matching in VBA and save the file as a TAB sep textfile and use bulk insert.

    But you can in DTS with VB script do things in Excel. There are a lot of examples on the Web.

    Just google on something like VBscript Excel create object.

    Gosta

  • November 13, 2008 at 1:46 pm

    #898296

    Gosta Munktell (11/11/2008)

    HI

    Hopefully you are aware of the problems with format when you import data from Excel

    with DTS or SSIS. I would do the pattern matching in VBA and save the file as a TAB sep textfile and use bulk insert.

    But you can in DTS with VB script do things in Excel. There are a lot of examples on the Web.

    Just google on something like VBscript Excel create object.

    Gosta

    Yes, I am more than aware of the shortcomings with DTS and Excel, been fighting them for quite a while. VBA is not an option, I need to keep the entire process contained in a DTS package. My thought would be to use ADODB to open a connection to the excel file, but I have no idea how to get a sheet name dynamically from the file. This is where I need help.

    thanks.

  • November 13, 2008 at 2:02 pm

    #898305

    "VBA is no alternative"

    Ok I understand your dilemma. But as VBScript is an integrated part of DTS you can develope

    in VBA and migrate to VBScript. I can help you more if you like this procedure.

    //Gosta

    You can create an Active X script like below and contect to a database etc.

    ' Visual Basic ActiveX Script

    '************************************************************************

    dim excelapp

    Function Main()

    Set excelapp = CreateObject("Excel.Application")

    'Set worksheet = CreateObject("Excel.sheet")

    excelapp.visible = true

    excelapp.Workbooks.Open "C:\Book1.xls"

    'excelapp.Worksheets("Sheet1").Copy

    'excelapp.Worksheets.add

    'excelapp.ActiveSheet.Paste

    excelapp.Cells.Select

    excelapp.Selection.Copy

    excelapp.Sheets.Add

    excelapp.Sheets("Sheet2").Select

    excelapp.Range("A1").Select

    excelapp.ActiveSheet.Paste

    Main = DTSTaskExecResult_Success

    End Function

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply