Post reply

DTS Excel Creation - Date Insert

  • August 28, 2007 at 3:56 am

    #118301

    Morning,

        I am developing a reporting system that will use DTS to populate a spreadsheet which will be visible (read only) over our Intranet.

      The Reports will be updated monthly and I want to populate a cell with the date the report is created i.e. when a Job is run to invoke the DTS package.

        I am sure that there must be some VB script that I can insert into a cell of the spreadsheet, but I am afraid my VB skills are limited(!)

       Can anybody assist? I have added VBA to the list of books I must read. 😉

    Thank you

    Colin

  • August 28, 2007 at 8:11 am

    #729786

    Going to be something like this

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Option Explicit

    Function Main()

     Dim oPkg, oConn, strPath, cntValue

     Set oPkg = DTSGlobalVariables.Parent

     cntValue = DTSGlobalVariables.[YourVariableYouStoredTheCounInHere]

     'Getting Excel object in use in DTS Package so I don't have to rewrite for changes in environment.'

     Set oConn = oPkg.Connections("FutureOpeningsXLS")

     strPath = oConn.Properties("DataSource").value

     Set oConn = nothing

     Set oPkg = nothing

     Dim XlsApp, XlsBook, XlsSheet

     

     Set XlsApp = CreateObject("Excel.Application")

     

     ' Open the workbook specified

     Set XlsBook = XlsApp.Workbooks.Open(strPath)

     Set XlsSheet = XlsBook.WorkSheets(1) ' Or you ca use sheet name like .WorkSheets("Sheet1")

     XlsSheet.Range("A1").Value = cntValue

     

     XlsBook.Save

     

     Set XlsSheet = Nothing

     XlsBook.Close

     Set XlsBook = Nothing

     XlsApp.Quit

     Set XlsApp = Nothing

     Main = DTSTaskExecResult_Success

    End Function

     

    However I woul suggest if you have ReportingServices installed you can do the same thing in RS and hav exported to Excel on a regular schedule. Plus later changs will be much easier.

  • August 29, 2007 at 1:26 am

    #729991

       Thank you. I assume that this will be the last step in the DTS package.

        Will need to do some more reading on the subject, but the principle is most welcome. We do not have Reporting Services installed and will be moving to SQL2005 shortly, so do not want to open too many "new worlds" inn one go. 😉

    Kind regards

    Colin

  • August 31, 2007 at 9:03 am

    #730808

      Have made progress with this, but get an error "ActiveX componenent can't create object: 'Excel.Application'

       Am I right in thinking that the server running the DTS must have Excel installed, as well as the target server holding the spreadsheet to be amended?

    thanks

    Colin

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

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