Rename Excel Files
 

Hi All,
I have a group of about 20 Excel files in C:\Cars.
All the files begin with 5 zeros. For example 00000_redcar.xls,
00000_bluecar.xls.
I need a macro to replace the zeros in each file with another 5 digit
number.
For example 00000_redcar.xls would change to 12345_redcar.xls,
00000_bluecar.xls would change to
12345_bluecar.xls etc for all the files in the C:\Cars directory.
Thanks so much for any help.
Timwell

Rename Excel Files
 

Using FSO object to manipulate file names, this provides a template. Note:
if file(s) are open or target name exists then you get an error, you need to
change code to improve.

Sub fName()
' set reference in vba project to Microsoft Scripting Runtime

Dim fso As New Scripting.FileSystemObject
Dim lFolder As Scripting.Folder
Dim lFile As Scripting.File

'set folder to act upon
Set lFolder = fso.GetFolder("C:\")

'loop through files
For Each lFile In lFolder.Files
If Left(lFile.Name, 5) = "00000" And Right(lFile.Name, 3) = "xls" Then
lFile.Name = "12435" & Mid(lFile.Name, 6, Len(lFile.Name) - 5)
End If
Next lFile

End Sub

--
Cheers
Nigel



"timwell" wrote in message
ups.com...
Hi All,
I have a group of about 20 Excel files in C:\Cars.
All the files begin with 5 zeros. For example 00000_redcar.xls,
00000_bluecar.xls.
I need a macro to replace the zeros in each file with another 5 digit
number.
For example 00000_redcar.xls would change to 12345_redcar.xls,
00000_bluecar.xls would change to
12345_bluecar.xls etc for all the files in the C:\Cars directory.
Thanks so much for any help.
Timwell

Rename Excel Files
 

Give it try.

Sub renamefile()
Dim fs, f, fc, f1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\Cars") '<<==Change if not
Set fc = f.Files
On Error GoTo ex
For Each f1 In fc
f1.Name = "12345" & Mid(f1.Name, InStr(f1.Name, "_"))
Next
Exit Sub
ex:
MsgBox "Can't change file name: " & f1.Name
Resume Next
End Sub

keizi

"timwell" wrote in message
ups.com...
Hi All,
I have a group of about 20 Excel files in C:\Cars.
All the files begin with 5 zeros. For example 00000_redcar.xls,
00000_bluecar.xls.
I need a macro to replace the zeros in each file with another 5 digit
number.
For example 00000_redcar.xls would change to 12345_redcar.xls,
00000_bluecar.xls would change to
12345_bluecar.xls etc for all the files in the C:\Cars directory.
Thanks so much for any help.
Timwell