DonationCoder.com Software > Post New Requests Here
Batch file or vbs code to change name of xls worksheet back to "Sheet1"
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
Go to full version