Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unhappy Unanswered: DTS to Create New Excel Sheet in Existing Workbook

    Has anyone been able to create a DTS that will create a new sheet within an existing Excel file. I want one Excel file with multiple tabs referencing the beginning of each week and growing. The name needs to be dynamic so as to not overwrite previous sheets (tabs) in the file.

    I have a current ActiveX script that will move the data to a dynamic name (date), I just can't create the table (sheet/tab) to move the data to.

    Any help would be appreciated.

    -Mark

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't think that you can...you probably need to use COM objects...

    Why do you need multiple sheets?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    I have a user asking for statistics in Excel with weekly tabs to compare. I may just build something in Crytal.

    Thanks,

  4. #4
    Join Date
    Aug 2003
    Posts
    39

  5. #5
    Join Date
    Jan 2004
    Posts
    65
    Multiple worksheets are done by creating multiple tables in your DTS package. Each table represents a worksheet.

    There you have it

  6. #6
    Join Date
    Feb 2004
    Posts
    4

    Talking

    To delete the sheet in your excel and re-create it
    you can do it in SQL task by including the following lines:

    DROP table `a43`
    GO

    CREATE TABLE `a43` (
    `Col001` VarChar (2) ,
    `Col002` VarChar (8) ,
    ...
    ...
    ... )

    where a43 is your sheet name ~
    hope this will help you out

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    As has been previously stated, create a connection to your workbook, then use an execute sql task on the connection. The sql task should be a create table statement. This will create your sheets in your work book.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •