August 23, 2006 at 4:52 am
Hi All,
I am trying to create a DTS package that connects to Excel, and within the Excel worksheet, I can have workbooks created dynamically.
EG: Report_20060822, Report_20060833
In the current system, I need to create one static worksheet and on the Excl worksheet, it needs to be dropped all the time and re-created.
Any advice would help
August 23, 2006 at 8:10 am
I asked one of our network guys to write me an ActiveX script to rename a file to include the date and delete files older than 3 days. In DTS, I write to a static name. I'm sure there are other ways, but this gets the job done for me.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
' rename nightly file with the date as part of the filename
Dim wFile
With CreateObject("scripting.filesystemobject")
Set wFile = .GetFile("\\info\department1\report.xls")
wFile.Name = "Report_" & Replace(Date, "/", "-") & ".xls"
End With
'
'
' delete old files
On Error Resume Next
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\info\department1")
Set colFiles = objFolder.files
'
For Each objFile In ColFiles
If Now - ObjFile.DateLastModified => 3 Then
objFile.Delete
End If
Next
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply