Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • October 01, 2016, 03:42:33 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Batch file or vbs code to change name of xls worksheet back to "Sheet1"  (Read 8383 times)

nkormanik

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 315
    • View Profile
    • Donate to Member
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

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 315
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #1 on: December 20, 2013, 06:55:37 PM »
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

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 315
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #2 on: December 20, 2013, 07:08:52 PM »
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

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,548
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #3 on: December 20, 2013, 11:13:20 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,410
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #4 on: December 21, 2013, 12:01:00 AM »
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 :)
« Last Edit: December 21, 2013, 05:09:31 AM by 4wd, Reason: Edit to fix use of object. »

nkormanik

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 315
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #5 on: December 21, 2013, 03:33:21 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,410
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #6 on: December 21, 2013, 04:31:40 AM »
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

  • Participant
  • Joined in 2010
  • *
  • default avatar
  • Posts: 315
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #7 on: December 21, 2013, 05:02:15 AM »
Outstanding, 4wd.  Worked perfectly.  One double-click and it took care of the entire huge job.

Thank you very, very much.


4wd

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,410
    • View Profile
    • Donate to Member
Re: Batch file or vbs code to change name of xls worksheet back to "Sheet1"
« Reply #8 on: December 21, 2013, 05:53:01 AM »