DTS Excel Creation - Date Insert

Question

Post reply

DTS Excel Creation - Date Insert

Colin Betteley

Hall of Fame

Points: 3095
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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.

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

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

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 3

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