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
Nigel - 30 Sep 2006 12:37 GMT
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

Signature
Cheers
Nigel
> Hi All,
> I have a group of about 20 Excel files in C:\Cars.
[quoted text clipped - 7 lines]
> Thanks so much for any help.
> Timwell
kounoike - 30 Sep 2006 12:47 GMT
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
> Hi All,
> I have a group of about 20 Excel files in C:\Cars.
[quoted text clipped - 7 lines]
> Thanks so much for any help.
> Timwell
Bob Phillips - 30 Sep 2006 14:06 GMT
Sub RenameFiles()
Dim sFileName As String
Const sStartDir As String = "C:\Cars"
Const sLookFor as String = "00000"
Const sReplace As String = "12345"
sFileName = Dir(sStartDir & "\*.xls")
Do Until sFileName = ""
If Left(sFileName, 5) = sLookFor Then
Name sStartDir & "\" & sFileName As Replace( _
sStartDir & "\" & sFileName, "00000", sReplace)
End If
sFileName = Dir
Loop
End Sub

Signature
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
> Hi All,
> I have a group of about 20 Excel files in C:\Cars.
[quoted text clipped - 7 lines]
> Thanks so much for any help.
> Timwell
timwell - 30 Sep 2006 16:36 GMT
Hi, Thank you all very much! Everything works wonderfully!
Timwell
> Sub RenameFiles()
> Dim sFileName As String
[quoted text clipped - 32 lines]
> > Thanks so much for any help.
> > Timwell