September 30, 2003 at 6:20 am
I have written a DTS Package to export to Excel. This works perfectly except I wish to overwrite the existing Excel data rather than append. How would I do this?
September 30, 2003 at 1:27 pm
I have not figured out a way to truncate the Excel “table” – DTS says it’s a no-no when you try it. This is what I do instead. Try this little ActiveX Script.
I use this to store a running month’s data dump for my user. I store my Excel templates in one place; write to my user’s workbook in another place. The file will be overwritten everyday until next month, and then it will create a new dump file.
I use the global variable to create the dump file path dynamically in a Dynamic Properties Task, which is the next step in the package.
=============================================================
Function Main()
Dim oFso
Set oFso = CreateObject("Scripting.FileSystemObject")
Dim sMonth, sDate, sSaveFile, sTemplate
If Len(Month(Date)) = 1 Then
sMonth = "0" & CStr(Month(Date))
Else
sMonth = CStr(Month(Date))
End If
sDate = CStr(Year(Date)) & sMonth
sTemplate = "\\MyServer\MyPath\Templates\DumpFile_TEMPLATE.xls"
sSaveFile = "\\UserServer\UserPath\DumpFile_" & sDate & ".xls"
oFso.CopyFile sTemplate, sSaveFile, True
Set oFso = Nothing
DTSGlobalVariables("gsSetPath") = sSaveFile
Main = DTSTaskExecResult_Success
End Function
J. Moseley
[font="Courier New"]ZenDada[/font]
October 1, 2003 at 3:50 am
Many thanks - that looks just the job
Stefan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply