Dier02
asked on
Batch rename excel files
I want to batch rename a series of excel files in a folder using a vbs file. How would I do this? I want to rename them 1,2,3,4,5....i.e 1.xls....
ASKER
OK then , a batch file will do. What is the code for that?
ASKER
No, it doesn't matter what number they are renamed or the order.
This is how I would do it.
From the command prompt in the same directory as the files
dir *.xls* /b >xlist.txt
This will create a list of files in the file called xlist.txt.
Open xlist.txt in excel
enter this formula in B1
="ren " & A1 & row() &".xls" & if(right(a1,1)<>"s",right( a1,1),"")
and copy it down.
Then move this column B to a batch file and run it.
From the command prompt in the same directory as the files
dir *.xls* /b >xlist.txt
This will create a list of files in the file called xlist.txt.
Open xlist.txt in excel
enter this formula in B1
="ren " & A1 & row() &".xls" & if(right(a1,1)<>"s",right(
and copy it down.
Then move this column B to a batch file and run it.
ASKER
Seems overly complicated for a batch name change.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then try this sub
Sub renserially()
pname = "D:\Temp\"
fname = Dir(pname & "*.xls")
flist = ""
Do While fname <> ""
flist = flist & fname & "|"
fname = Dir()
Loop
flist = Split(Left(flist, Len(flist) - 1), "|")
For Each fname In flist
ctr = ctr + 1
Name pname & fname As pname & ctr & ".xls"
Next fname
End Sub
Sub renserially()
pname = "D:\Temp\"
fname = Dir(pname & "*.xls")
flist = ""
Do While fname <> ""
flist = flist & fname & "|"
fname = Dir()
Loop
flist = Split(Left(flist, Len(flist) - 1), "|")
For Each fname In flist
ctr = ctr + 1
Name pname & fname As pname & ctr & ".xls"
Next fname
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is the batch file approach. It is simplified in regard of extensions to care about, but should suffice.
@echo off
setlocal EnableDelayedExpansion
pushd C:\MyExcelFiles
set i=1
for %%F in (*.xls? *.xlt? *.xlam) do (
ren "%%~fF" "%%~dpF!i!%%~xF"
set /A i += 1
)
popd
I've requested that this question be closed as follows:
Accepted answer: 250 points for CharlWiehahn's comment http:/Q_27409872.html#37010750
Assisted answer: 250 points for Qlemo's comment http:/Q_27409872.html#37010801
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 250 points for CharlWiehahn's comment http:/Q_27409872.html#37010750
Assisted answer: 250 points for Qlemo's comment http:/Q_27409872.html#37010801
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Why not ssaqibh #37010729 as well?
Strictly seen only http:#a37010728 and/or http:#a37010750 fulfill the original question "using a vbs file". But http:#a37010134 extended that to allow for (cmd) batch files.
It might be picky, but the asker never stated a VB solution, needing to be included in a Excel VBA code module, would be sufficient. And IMO it isn't, and the code presented is not even elegant.
I (as fellow Cleanup Volunteer) would have chosen the same answers as teylyn, as those seem to fit best.
It might be picky, but the asker never stated a VB solution, needing to be included in a Excel VBA code module, would be sufficient. And IMO it isn't, and the code presented is not even elegant.
I (as fellow Cleanup Volunteer) would have chosen the same answers as teylyn, as those seem to fit best.
~bp