Post reply

How to have the DTS replaces the original rows in excel?

  • December 13, 2005 at 3:49 pm

    #67666

    Hi experts,

    I am using DTS package to load SQL Server 2000 data into an Excel template.  The data will be loaded to the hidden excel sheet and the data will be caculated/populated to the formated template.  However, my DTS package appends new data to the original rows on the hidden excel sheet, and I need the DTS to replace the original rows.  How can I do that? - I don't want to drop and recreate the destination excel sheet!

    Thank you in advance.

  • December 13, 2005 at 4:11 pm

    #609336

    The Excel driver does not support deleting worksheet rows. Since you don't want to recreate the destination XLS file, your other option is to use an ActiveX script task, and invoke the Excel automation objects.

    Use the Micosoft.Public.SqlServer.DTS group search, and search on keywords "Delete Excel" - you'll find sample VBScript code in the 1st couple of search hits:

    http://groups.google.com/group/microsoft.public.sqlserver.dts/search?group=microsoft.public.sqlserver.dts&q=delete+excel&qt_g=1

     

  • December 14, 2005 at 2:39 am

    #609407

    Hi,

    the best way - in my opinion - is to drop the table in the XLS file and then to create the table (statement can be copied from the transform data task).

    Excel normally keeps all the formating inside the sheet.

    I haven't tried it with a hidden sheet, but it should work as well.

    Matthias 

Viewing 3 posts - 1 through 2 (of 2 total)

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