I have 286 tables which each has different worksheets name (30 character )
and contains space such as : "Zone 1 San Diego 2003_12_08 to "
and I have to change all of those worksheets into a name like sheet1
Is there any batch files to change all those files at once using ActiveX
script or any other methods?

For Each sh In Activeworkbbok.Worksheets
sh.NAme = "Sheet" & i+1
i = i + 1
Next sh

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Senjaya" wrote in message
...
I have 286 tables which each has different worksheets name (30 character )
and contains space such as : "Zone 1 San Diego 2003_12_08 to "
and I have to change all of those worksheets into a name like sheet1
Is there any batch files to change all those files at once using ActiveX
script or any other methods?

Sub ReNameSheets()
Dim wks As Worksheet
Dim intName As Integer

intName = 1
For Each wks In Worksheets
wks.Name = "Sheet" & intName
intName = intName + 1
Next wks

End Sub

HTH

"Senjaya" wrote:

I have 286 tables which each has different worksheets name (30 character )
and contains space such as : "Zone 1 San Diego 2003_12_08 to "
and I have to change all of those worksheets into a name like sheet1
Is there any batch files to change all those files at once using ActiveX
script or any other methods?

Actually I have to apply the code to the DTS package which I realize
only can accept the VB ActiveX script only not the regular VB code.
And on each excel file only have 1 worksheet.
I try this methods below to rename them but somehow have a problem to
rename worksheet which have 30 char such as :
"Zone 1 San Diego 2003_12_08 to "


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

Please help again and thank in advance -

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