SQL Server Performance

Creating Excel Sheet

Discussion in 'SQL Server DTS-Related Questions' started by danny123, May 24, 2007.

  1. danny123 New Member

    Hi there,

    I am trying to create a dts package which will save the results in a excel sheet. Is there a way that the dts create a new excel file itself every time it run.

    Thanks
    Danny
  2. MohammedU New Member

    You can sue SP_OAxxx procedure to do that....
    or
    you can keep one blank excel sheet and copy it as needed using xp-cmdshell...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. satya Moderator

    Be aware if you are using office automation then ensure to have upto date service packs on SQL server and client side too including on Office pack.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. danny123 New Member

    Thanks for your suggestions,

    I used a activeX script which will create a new excel sheet each time the DTS package will run.

    strFileName = "C:ScriptsWeeksBirthDay.xls"

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True

    Set objWorkbook = objExcel.Workbooks.Add()
    objWorkbook.SaveAs(strFileName)

    objExcel.Quit

    Please let me know if this method have any negetive points.

    Thanks
    Danny
  5. satya Moderator

    If you are getting expected results with no issues, then why not. Go for it, I don't see any specific reason. IMHO it is better to use Activex than SP_OAxx types.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. MohammedU New Member

    I agree with Saya, using ActiveX instead of sp_oaXX procedures...
    Most of the times I don't use either of them...
    I copy one sample file and use that one to make new one using nt copy command....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page