DonationCoder.com Software > Post New Requests Here

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

(1/2) > >>

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.com/questions/17050457/renaming-and-formatting-excel-worksheet-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.extendoffice.com/documents/excel/609-excel-rename-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 ---for /r %1 %%a in (data.xls) do  (cscript.exe ExcelWSRename.vbs "%%~fa")
ExcelWSRename.vbs

--- Code: ActionScript ---'check for argsif WScript.Arguments.Count = 0 then    WScript.Echo "Missing parameters"    WScript.Quitend if 'launch Excel and open fileSet xlObj = CreateObject("Excel.Application")Set xlFile = xlObj.WorkBooks.Open(WScript.Arguments(0))'turn off screen alertsxlObj.Application.DisplayAlerts = FalseSet xlWS = xlFile.Worksheets(1)xlWS.Name = "Sheet1"'save, close, then quitxlFile.Close TruexlObj.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 :)

Navigation

[0] Message Index

[#] Next page