Batch file or vbs code to change name of xls worksheet back to "Sheet1"

[nkormanik]

Because I divided many large xls workbooks (single worksheet only) into multiple worksheets, each worksheet (after being separately saved) has a unique name, not the standard "Sheet1."

Another program I'm using (SAS) is expecting each worksheet name to be "Sheet1."

I can, of course, manually go into each of the workbooks and rename the worksheet back to "Sheet1."

Would be lots easier, though, to have a batch file, vbs code, ahk code, or something, to place in respective folder, double-click, and do the name-change automatically.

In present case all the workbooks (in different folders) have the same name:  data.xls

Only one worksheet per workbook, worksheets have various names (like 15-1, 17-3, etc.).

Would someone please write the code that changes first worksheet (and only) in a workbook to "Sheet1"?

Note:  If it would be necessary to edit code to put the name of existing worksheet, then never mind.  That would be just as much work as manually renaming worksheets.

Any thoughts appreciated.

Thanks.

Nicholas Kormanik

[nkormanik]

Here is vbs code that renames a worksheet, and various other things.  Note, unfortunately, that one must include the present name of worksheet.  That won't do in my present case, as I might as well just manually go in and rename....

http://stackoverflow...heet-with-batch-file

'launch Excel and open file
Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("c:\temp\filename.xls")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets
   'change sheet to desired worksheet name
   If Worksheet.Name = "SheetToRename" Then
      Worksheet.Name = "NewName"
   End If
   'change all sheets to desired font
   With Worksheet.Cells.Font
       .Name = "Verdana"
       .Size = 12
   End With
Next 
'save, close, then quit
xlFile.Close True
xlObj.Quit

[nkormanik]

Another possible lead:

http://www.extendoff...name-worksheets.html

VBA code to rename multiple worksheets by the name you want at once

Using the following VBA code, you can quickly rename all worksheets of the current workbook with the same prefix in their worksheet names, such as: KTE-order1, KTE-order 2, and KTE-order 3…

Step 1: Click on Developer > Visual Basic, and click Insert > Module in the Microsoft Visual Basic Application Windows.

Step 2: Please copy and paste the following code into the Module.

Sub ChangeWorkSheetName()
Dim WS As Worksheet
i = 1
On Error Resume Next
For Each WS In Worksheets
WS.Name = "KTE-order"&i
i = i + 1
Next
End Sub

[TaoPhoenix]

I'll suggest an ugly hack.

Using one of the "macro scripting" programs, you might be able to do some version of this:
1. Initial Windows search for .xls or .xlsx files aka Excel
2. Alt-____-_____ go to first tab to Rename
3. Control-V Paste the correct new name

I just looked at the problem in Kingsoft, so I'm missing the exact commands in Excel, but at the key point there has to be a command to "go to first tab", then "rename", and then it's Control-V Paste. "Ok-Ok-Save-Close" at the very end.

[4wd]

Put these two files, (.cmd and .vbs), in the same directory.

ExcelWSR.cmd

Code: Text [Select]

1. for /r %1 %%a in (data.xls) do  (cscript.exe ExcelWSRename.vbs "%%~fa")

ExcelWSRename.vbs

Code: ActionScript [Select]

1. 'check for args
2. if WScript.Arguments.Count = 0 then
3.    WScript.Echo "Missing parameters"
4.    WScript.Quit
5. end if
6.  
7. 'launch Excel and open file
8. Set xlObj = CreateObject("Excel.Application")
9. Set xlFile = xlObj.WorkBooks.Open(WScript.Arguments(0))
10. 'turn off screen alerts
11. xlObj.Application.DisplayAlerts = False
12. Set xlWS = xlFile.Worksheets(1)
13. xlWS.Name = "Sheet1"
14. 'save, close, then quit
15. xlFile.Close True
16. xlObj.Quit

Usage:
ExcelWSR.cmd <path>   <- It'll recurse through the tree

eg. ExcelWSR.cmd D:\data

Test it on a sacrificial file first since I don't have Excel, nor do I use VBScript

[nkormanik]

Seemed enormously promising, 4wd.  Hopefully just a tweak and it'll work.  Did as instructed.  In command window here's the error message continually received with each recursion/iteration:

C:\6>(cscript.exe ExcelWSRename.vbs "c:\6\15-3\data.xls" )
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

C:\6\ExcelWSRename.vbs(12, 1) Microsoft VBScript runtime error: Type mismatch: 'Sheet'


C:\6>(cscript.exe ExcelWSRename.vbs "c:\6\15-4\data.xls" )
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

C:\6\ExcelWSRename.vbs(12, 1) Microsoft VBScript runtime error: Type mismatch: 'Sheet'


C:\6>(cscript.exe ExcelWSRename.vbs "c:\6\15-5\data.xls" )
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

C:\6\ExcelWSRename.vbs(12, 1) Microsoft VBScript runtime error: Type mismatch: 'Sheet'

[4wd]

Looks like I got the wrong object name ... time to read a bit more.

Ahh, I was using the VBA object instead of declaring in VBS before using, (I think) ... edited VBS above.

[nkormanik]

Outstanding, 4wd.  Worked perfectly.  One double-click and it took care of the entire huge job.

Thank you very, very much.

[4wd]

The FYI section:

Can I pass an argument to a VBScript
QTP - How to rename Excel worksheet
DOS For command

+ your second post.