DonationCoder.com Software > Post New Requests Here

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

<< < (2/2)

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 :)

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.

 ;)

Navigation

[0] Message Index

[*] Previous page