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
|