Loop through a folder and rename the files

[Keala]

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

 

[John_w]

Try this macro on a copy of your folder, modifying the code where indicated to change the path variable.

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

 

[Yongle]

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
Test on a copy of your folder

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

 

[Rick Rothstein]

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
Test on a copy of your folder

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...

[table="width: 500"]
[tr]
	[td]Name fPath & myFile As fPath & Replace(Cells(1, a).Address(0, 0), 1, "_") & myFile[/td]
[/tr]
[/table]

 

[Yongle]

No better, no worse, simply an alternative way to write the red highlighted line of code for those who might be interested...

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

 

[John_w]

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.

 

[Yongle]

oops!

 

[Rick Rothstein]

oops!

Double OOPS!!!

 

[Keala]

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.

 

[Yongle]

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