Loop through a folder and rename the files

Keala

New Member
Joined
Jul 9, 2018
Messages
37
I'm kind of new to VBA and wonder if there is a easy way to loop through a folder with let say 300 files add a letter in alphabetic order at the beginning of each file name. So if the file name is "Vpp_vs_Freq_at_PC-10.0_KKC-2.0_180817-143105" change the name to "A_Vpp_vs_Freq_at_PC-10.0_KKC-2.0_180817-143105", next to "B_Vpp_vs_Freq_at_AC-10.0_DC-2.0_180817-143108" and so on when it reach Z add ZA and so on until next time reach Z then add ZZA...

I have search for some hints but not really been able to find anything which matches what I'm looking for. I will appreciate if you can give me a suggestion or a direction where I can find the right information.

Thank you
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try this macro on a copy of your folder, modifying the code where indicated to change the path variable.
Code:
Public Sub Rename_Files_in_Folder()

    Dim path As String, filename As String
    Dim n As Long
   
    path = "C:\path\to\folder\"               'MODIFY THIS LINE - FOLDER CONTAINING FILES TO BE RENAMED
    
    If Right(path, 1) <> "\" Then path = path & "\"
    n = 0
    filename = Dir(path & "*.*")
    Do While filename <> vbNullString
        Name path & filename As path & String(n \ 26, "Z") & Chr(n Mod 26 + 65) & "_" & filename
        filename = Dir
        n = n + 1
    Loop

End Sub
 
Upvote 0
Very similar to @John_w but sufficiently different to warrant posting

Amend the folder path - include "\" at the end which avoids having to insert elsewhere
:warning: Test on a copy of your folder

Code:
Sub RenameFiles()
    Const fPath = "C:\Folder\SubFolder[COLOR=#ff0000]\[/COLOR]"                 'end path with[COLOR=#ff0000] "\"[/COLOR]
    Dim a As Long, myFile As String
    myFile = Dir(fPath & "*.*")
    a = 0
    Do While myFile <> ""
        a = a + 1
        Name fPath & myFile As fPath & Split(Cells(1, a).Address, "$")(1) & "_" & myFile
        myFile = Dir
    Loop
End Sub
 
Last edited:
Upvote 0
Very similar to @John_w but sufficiently different to warrant posting

Amend the folder path - include "\" at the end which avoids having to insert elsewhere
:warning: Test on a copy of your folder

Code:
Sub RenameFiles()
    Const fPath = "C:\Folder\SubFolder[COLOR=#ff0000]\[/COLOR]"                 'end path with[COLOR=#ff0000] "\"[/COLOR]
    Dim a As Long, myFile As String
    myFile = Dir(fPath & "*.*")
    a = 0
    Do While myFile <> ""
        a = a + 1
        [B][COLOR="#FF0000"]Name fPath & myFile As fPath & Split(Cells(1, a).Address, "$")(1) & "_" & myFile[/COLOR][/B]
        myFile = Dir
    Loop
End Sub
No better, no worse, simply an alternative way to write the red highlighted line of code for those who might be interested...
Code:
[table="width: 500"]
[tr]
	[td]Name fPath & myFile As fPath & Replace(Cells(1, a).Address(0, 0), 1, "_") & myFile[/td]
[/tr]
[/table]
 
Upvote 0
No better, no worse, simply an alternative way to write the red highlighted line of code for those who might be interested...
Code:
Name fPath & myFile As fPath & Replace(Cells(1, a).Address(0, 0), 1, "_") & myFile
@Rick Rothstein - many ways to "cook the crumble" but yours is simpler to understand and therefore gets my vote :)
 
Upvote 0
Very similar to @John_w but sufficiently different to warrant posting
My understanding is that the OP asked for A_, B_, ... Z_, then ZA_, ZB_, ... ZZ_, then ZZA_ etc.

However your code (and Rick's) generates A_, B_, ... Z_, then AA_, AB_, ... AZ_, etc.
 
Upvote 0
Thank you everyone for your suggestions. John_w suggestion is the most like what I want to achieve. But unfortunately something happens with the code when it reaches Z then the file names become:

ZZ_Waveform_Bursts-55_Freq-152.0_PC-30.0_KKC-6.0_180817-170846
ZZZ_Waveform_Bursts-55_Freq-153.0_PC-20.0_KKC-10.0_180817-190524
ZZZZ_W_Waveform_Bursts-55_Freq-152.0_PC-30.0_KKC-16.0_180817-223256
ZZZZZ_ZX_Waveform_Bursts-55_Freq-153.0_PC-10.0_KKC-6.0_180817-164250
ZZZZZZ_ZZZB_Y_Waveform_Bursts-55_Freq-152.0_PC-30.0_KKC-4.0_180817-160350
ZZZZZZZ_ZZZZD_ZZD_Waveform_Bursts-55_Freq-153.0_PC-20.0_KKC-2.0_180817-144616
ZZZZZZZZ_ZZZZZF_ZZZH_ZF_Waveform_Bursts-55_Freq-152.0_PC-40.0_KKC-2.0_180817-151202
.
.
.
last file
ZZZZZZZZZZZZZZZZZZZT_ZZZZZZZZZZZZZZZZZY_ZZZZZZZZZZZZZZZZA_ZZZZZZZZZZZZZZZA_ZZZZZZZZZZZZZX_ZZZZZZZZZZZQ_ZZZZZZZZZE_ZZZZZZZO_ZZZZZV_ZZV_Waveform_Bursts-55_Freq-153.0_PC-40.0_KKC-6.0_180817-172152

What I want to do as you state is A_, B_, ... Z_, then ZA_, ZB_, ... ZZ_, then ZZA_ etc. Could you please let me know what need to be changed for improving the code? Further by end a popup with Run-time '53' appears with text "File not found" and reference it to the line "Name path & filename As path & String(n \ 26, "Z") & Chr(n Mod 26 + 65) & "_" & filename"?

Thank you for the clarifications.
 
Upvote 0
I suspect that some renamed file are being renamed again
- are they going to the bottom of the list by putting Z in front of them and being picked up a second time?
- should capture the names in an array first
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top