Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Question about deleting a spreadsheet in excel using DTS

    Dear all,

    i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!

    Kam2

  2. #2
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22

    Re: Question about deleting a spreadsheet in excel using DTS

    Use a combination of Drop/Create Table statements within a SQL-Task before pumping data to excel.

    Note the type of quote marks!!

    E.g.:

    DROP table `tb_Excel_Output`
    GO
    CREATE TABLE `tb_Excel_Output` (
    `Col1` VarChar (7) ,
    `Col2` VarChar (2) ,
    `Col3` VarChar (3) )


    kbk

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    Thx

    your method rocks ~!
    thx for you help i'm just an newbie in DTS

  4. #4
    Join Date
    Nov 2004
    Posts
    6
    When I try to drop an Excel table using this method, I'm receiving a "Microsoft Jet Database Engine" error stating that I'm using invalid syntax in the drop statement.

    The only SQL I'm using is:
    DROP table 'x'
    GO

    I'm using the connection that I've created for the Excel spreadsheet.

    Is there something I'm missing?

    Thanks,
    Steve
    Last edited by hanzelmans; 10-11-05 at 08:57.

Posting Permissions

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