We used the DTS looping procedure to transfer csv files and everything
was fine - but when we used for transfering excel files, it stop on the
first file. We check and we realize on each files, the rename the
worksheet from sheet1 to any other name such as "Zone 1 San Diego
2003_12_08 to "
That's why we have this code below to rename the worksheet back.
But somehow it did not work.
Please help us to configure the proper function to make it work.
Thanks.


Function Main()
'use script to rename all sheets to a common name
dim xlObj
Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open
DTSGlobalVariables("gv_FileFullName").value
xlObj.ActiveWorkbook.Sheets(1).Name = "DataSheet"
xlObj.ActiveWorkbook.Save
xlObj.Quit
Set xlObj = Nothing
Main = DTSTaskExecResult_Success
End Function

If you change the Excel sheet name then you will need to change the SourceObjectName in the datapump task as well as the file name.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"KSSG" <hariantos (AT) kssg (DOT) com> wrote

The problem is the excel did not allow us to change the worksheet name
even we put the "right" code we think to force them to change the name.
The SourceObjectName is change to DataSheet$ as we wanted
since the Excel worksheet name is still the same- it did not want to go
thru the loop and give us error- do not find the specified file name



*** Sent via Developersdex http://www.developersdex.com ***