Post reply

Inserting to Excel templates

  • July 9, 2004 at 6:20 am

    #59715

    Hi

    I have limited experience with DTS.  I want to create a package that connects to the db, run my queries, insert the results into temp tables, where I can format the results further.  Then insert the data into Excel.  Simple enough...

    But my question is, whats the best way to insert the data into an excel template?  Is it possible to specify where the data is inserted on the spreadsheet.  As there are numerous formulas that I don't want to be overwritten.  Basically trying to automate a report(s) that takes an hour to produce manually.

    TIA

    Ritch


    "I didn't do anything it just got complicated" - M Edwards

  • July 9, 2004 at 11:28 am

    #514154

    DTS treats Excel like a table.  Just dump your data into a data page and let your formulas pull from there.

    http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp

    Create a template Workbook where DTS can pick it up.  You will need an ActiveX script and the FileSystemObject to copy the template, name it and save it somewhere.  Write the file path to a Global Variable.  After the ActiveX task, use a Dynamic Properties Task.  Here you will assign the DataSource property of the Excel connection object to the new path.  Then create a transformation from a SQL Server connection to the Excel connection and dump your data to the data page. 

     

    [font="Courier New"]ZenDada[/font]

  • July 11, 2004 at 8:55 am

    #514212

    Thanks for the pointers Jules.

    Cheers

    Ritch


    "I didn't do anything it just got complicated" - M Edwards

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

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