Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I rename files that are related. | Excel Discussion (Misc queries) | |||
Help to rename files | Excel Worksheet Functions | |||
Rename Files according to Excel | Excel Programming | |||
Rename Files | Excel Programming | |||
Need Macro to rename excel files | Excel Programming |