Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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....
0
Dier02
Asked:
Dier02
  • 3
  • 3
  • 2
  • +3
2 Solutions
 
Bill PrewCommented:
Does it matter what files get renamed to which numbers?  Does it have to be done in VBS, a BAT file could do this very easily in a few lines of code.

~bp
0
 
Dier02Author Commented:
OK then , a batch file will do.  What is the code for that?
0
 
Dier02Author Commented:
No, it doesn't matter what number they are renamed or the order.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Saqib Husain, SyedEngineerCommented:
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.

0
 
Dier02Author Commented:
Seems overly complicated for a batch name change.
0
 
CharlWiehahnCommented:
Hi Dier02,

Here is a VBScript version.


objStartFolder = "C:\"
Count = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(objStartFolder)
Set colFiles = objFolder.Files

For Each objFile in colFiles
	If ucase(Right(objFile.Name,4)) = ".XLS" Then
		objFile.Name = Count &".xls"
		Count = Count + 1
	End If
Next

Wscript.echo "Done"

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
CharlWiehahnCommented:
Hi Dier02,

This version of the vbs code will also include later versions of Excel in the rename. You need to only update the first line to the path where your excel files are located.
objStartFolder = "C:\YourFolderHere"
Count = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(objStartFolder)
Set colFiles = objFolder.Files

For Each objFile in colFiles
	SFile = lcase(objFile.Name)
	If Right(SFile,4) = ".xls" Then			' MS Excel 97/2000/2003
		objFile.Name = Count &".xls"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xlsx" Then	'Excel 2007/10 XML Workbook
		objFile.Name = Count &".xlsx"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xlsm" Then	'Excel 2007/10 XML Macro-Enabled Workbook
		objFile.Name = Count &".xlsm"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xltx" Then	'Excel 2007/10 XML Template
		objFile.Name = Count &".xltx"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xltm" Then	'Excel 2007/10 XML Macro-Enabled Template
		objFile.Name = Count &".xltm"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xlsb" Then	'Excel 2007/10 binary workbook (BIFF12)
		objFile.Name = Count &".xltm"
		Count = Count + 1
	Elseif  Right(SFile,5) = ".xlam" Then	'Excel 2007/10 XML Macro-Enabled Add-In
		objFile.Name = Count &".xlam"
		Count = Count + 1
	End If
Next

Wscript.echo "Done " &Count - 1

Open in new window

0
 
QlemoC++ DeveloperCommented:
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

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
Why not ssaqibh #37010729 as well?
0
 
QlemoC++ DeveloperCommented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now