Rename_file names

[Keala]

I have several hundred files in a folder which I need to rename in a specific order. I write the question as it was only 12 files in the folder.
I need to add letter A_ to the first file name(0) in the folder, then letter B_ to the fourth file, letter C_ to the eighth. Then restart the loop on file two (in original) folder structure and add D_ to it then add E_ to file fifth and so on. Until Z, then add ZZA and so on. Please see below example. (I have so I can add A_ at first file, then B_ to the second file and so on)

Thank you for your help.

This is what I have:
Wf_B_PC-5.0_KC-5.0_180913-214228
Wf_B_PC-5.0_KC-10.0_180913-214228
Wf_B_PC-5.0_KC-15.0_180913-214228
Wf_B_PC-5.0_KC-20.0_180913-214228
Wf_B_PC-10.0_KC-5.0_180913-214228
Wf_B_PC-10.0_KC-10.0_180913-214228
Wf_B_PC-10.0_KC-15.0_180913-214228
Wf_B_PC-10.0_KC-20.0_180913-214228
Wf_B_PC-15.0_KC-5.0_180913-214228
Wf_B_PC-15.0_KC-10.0_180913-214228
Wf_B_PC-15.0_KC-15.0_180913-214228
Wf_B_PC-15.0_KC-20.0_180913-214228

This is what I want to get (Unsorted):

A_Wf_B_PC-5.0_KC-5.0_180913-214228
D_Wf_B_PC-5.0_KC-10.0_180913-214228
G_Wf_B_PC-5.0_KC-15.0_180913-214228
J_Wf_B_PC-5.0_KC-20.0_180913-214228
B_Wf_B_PC-10.0_KC-5.0_180913-214228
E_Wf_B_PC-10.0_KC-10.0_180913-214228
H_Wf_B_PC-10.0_KC-15.0_180913-214228
K_Wf_B_PC-10.0_KC-20.0_180913-214228
C_Wf_B_PC-15.0_KC-5.0_180913-214228
F_Wf_B_PC-15.0_KC-10.0_180913-214228
I_Wf_B_PC-15.0_KC-15.0_180913-214228
L_Wf_B_PC-15.0_KC-20.0_180913-214228

 

[ravishankar]

this little set of codes generate the sequence you wish to. add NAME xxxxxx AS yyyyyyyy and see if you can rename it.

Sub DDDD()
Dim a As Integer, b As Integer, c As Integer, d As Integer
c = 1
For a = 1 To 26
For d = 1 To 4
b = d * 3
Cells(c, 1) = Chr(a + b + 61)
c = c + 1
Next d
Next a
MsgBox "complete"
End Sub

ravi

 

[Keala]

Hi Ravi,

Thank you for your suggestion. But I'm not really sure where I should add Name xxxx and name yyyy; in your program or i a worksheet cell?

I don't want to rename file in worksheet or similar. I have a folder with several hundred *.csv files which I want to rename the file name in the folder according to the above question. Could you please highlight how I can implement your code for this purpose.

(I want to do this because I want to extract data from each *.csv file and move the data to a different sheet but not in the order which explore put the files or by time sets, but in the order as
A_Wf_B_PC-5.0_KC-5.0_180913-214228
B_Wf_B_PC-10.0_KC-5.0_180913-214228
C_Wf_B_PC-15.0_KC-5.0_180913-214228
... which will be the output of your program.)

Thank you,

 

[lrobbo314]

How are you getting your original file names? Do you have them in your worksheet already? Or do you need the code to go through the files on its own?

 

[lrobbo314]

OK. This was a weird one, but I think this should work. I have commented the code for the lines you will need to change. I created a folder on my desktop and created the same original text files with the names you gave in the original post. I also created an empty folder called target on my desktop. After running the code below, the files were renamed just like how your desired results looked. You will need to change the original and target folder paths in the code. This is a small sample too, so hopefully it will keep working with even more files. Here's the code, let me know how it works for you.

Sub Main()
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim Pattern As String: Pattern = "\w+PC-(\d+.\d{1})\w+KC-(\d+.\d{1})\w+"
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fold As Object: Set Fold = FSO.getfolder("C:\Users\Desktop\Original") 'Change to folder with files
Dim Fil As Object
Dim AR() As Variant: ReDim AR(1 To Fold.Files.Count, 1 To 2)
Dim tFil() As Variant: ReDim tFil(1 To UBound(AR, 1))
Dim Cnt As Long: Cnt = 1
Dim tStr As String
Dim lLng As Long


With RX
    .Pattern = Pattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    
    For Each Fil In Fold.Files
        Set matches = .Execute(Fil.Name)
        AR(Cnt, 1) = Fil.Name
        AR(Cnt, 2) = (matches(0).submatches(0) * 10000) + matches(0).submatches(1)
        Cnt = Cnt + 1
    Next Fil
    
    For i = 1 To UBound(AR, 1)
        For j = 1 To UBound(AR, 1)
            If AR(i, 2) < AR(j, 2) Then
                tStr = AR(i, 1)
                AR(i, 1) = AR(j, 1)
                AR(j, 1) = tStr
                tlng = AR(i, 2)
                AR(i, 2) = AR(j, 2)
                AR(j, 2) = tlng
            End If
        Next j
    Next i
    
    Cnt = 1
    
    For k = 1 To UBound(tFil)
        tFil(k) = GetColumn(Cnt) & "_" & AR(k, 1)
        Cnt = Cnt + 3
            If k Mod 4 = 0 Then
            Cnt = Cnt - 11
        End If
    Next k
    
    For x = 1 To UBound(tFil)
        Name Fold.Path & "\" & AR(x, 1) As "C:\Users\Desktop\Target\" & tFil(x) 'Change address to folder where files need to be saved to
    Next x
    
End With
        
End Sub


Function GetColumn(IDX As Long)
Dim col As Long
Dim Level1 As Integer
Dim Level2 As Integer
Dim Level3 As Integer
Dim A As String
Dim B As String
Dim C As String


col = IDX - 1
Level1 = ((col) Mod 26) + 1
 If col < 26 Then Level2 = 0 Else Level2 = (Int((col - 26) / 26) Mod 26) + 1
If col < 702 Then Level3 = 0 Else Level3 = Int(((col - 1) / 676))
If Level3 > 0 Then C = Chr(Level3 + 64)
If Level2 > 0 Then B = Chr(Level2 + 64)
A = Chr(Level1 + 64)
GetColumn = Trim(C & B & A)


End Function

 

[lrobbo314]

Also, I recommend creating a backup of your original folder and running the code on that folder. If something does go wrong with the code, you don't want your actual files to get screwed up. Run the code on a backup folder, make sure it works.

 

[Keala]

Thank you so much for the suggestion, unfortunately it give me "Run-time error 5: Invalid procedure call orargument" and highlight this line in the code

"AR(Cnt, 2) = (matches(0).submatches(0) * 10000) + matches(0).submatches(1) in the "For Each Fil In Fold.files"

Could you please let me know what I have done which is causing this.

Thank you,

 

[lrobbo314]

Run the code below and look in the 'Immediate Window' of the VB Editor, and post the results here so I can take a look at the what is going on.

Sub ListFiles()
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fold As Object: Set Fold = FSO.getfolder("C:\Users\EBAAJ7O\Desktop\Original") 'Change to folder with files
Dim Fil As Object


    For Each Fil In Fold.Files
        Debug.Print Fil.Name
    Next Fil
End Sub

 

[John_w]

I have several hundred files in a folder which I need to rename in a specific order. I write the question as it was only 12 files in the folder.
I need to add letter A_ to the first file name(0) in the folder, then letter B_ to the fourth file, letter C_ to the eighth. Then restart the loop on file two (in original) folder structure and add D_ to it then add E_ to file fifth and so on. Until Z, then add ZZA and so on. Please see below example. (I have so I can add A_ at first file, then B_ to the second file and so on)

Thank you for your help.

This is what I have:
Wf_B_PC-5.0_KC-5.0_180913-214228
Wf_B_PC-5.0_KC-10.0_180913-214228
Wf_B_PC-5.0_KC-15.0_180913-214228
Wf_B_PC-5.0_KC-20.0_180913-214228
Wf_B_PC-10.0_KC-5.0_180913-214228
Wf_B_PC-10.0_KC-10.0_180913-214228
Wf_B_PC-10.0_KC-15.0_180913-214228
Wf_B_PC-10.0_KC-20.0_180913-214228
Wf_B_PC-15.0_KC-5.0_180913-214228
Wf_B_PC-15.0_KC-10.0_180913-214228
Wf_B_PC-15.0_KC-15.0_180913-214228
Wf_B_PC-15.0_KC-20.0_180913-214228

This is what I want to get (Unsorted):

A_Wf_B_PC-5.0_KC-5.0_180913-214228
D_Wf_B_PC-5.0_KC-10.0_180913-214228
G_Wf_B_PC-5.0_KC-15.0_180913-214228
J_Wf_B_PC-5.0_KC-20.0_180913-214228
B_Wf_B_PC-10.0_KC-5.0_180913-214228
E_Wf_B_PC-10.0_KC-10.0_180913-214228
H_Wf_B_PC-10.0_KC-15.0_180913-214228
K_Wf_B_PC-10.0_KC-20.0_180913-214228
C_Wf_B_PC-15.0_KC-5.0_180913-214228
F_Wf_B_PC-15.0_KC-10.0_180913-214228
I_Wf_B_PC-15.0_KC-15.0_180913-214228
L_Wf_B_PC-15.0_KC-20.0_180913-214228

This task is similar to the one I helped you with - https://www.mrexcel.com/forum/excel...der-rename-files-post5132076.html#post5132076 - but with a different sequence of letters prepended to the file names. How does the sequence of letters continue?

As I see it, this follows the pattern you want:

A D G J,  B E H  K,   C F I   L,    M  P  S  V,   N  Q  T  W,   O  R  U  X,    Y ZB ZE ZH,   Z ZC ZF ZI,  ZA ZD ZG ZJ
0 3 6 9,  1 4 7 10,   2 5 8  11,   12 15 18 21,  13 16 19 22,  14 17 20 23,   24  1  4  7,  25  2  5  8,   0  3  6  9
0 1 2 3,  4 5 6  7,   8 9 10 11,   12 13 14 15,  16 17 18 19,  20 21 22 23,   24 25 26 27,  28 29 30 31,  32 33 34 35

The 0 3 6 9, etc. are the index numbers of the letters in the alphabet (zero based), ignoring the added Z's.
The 0 1 2 3, etc. are the index numbers of the file names (zero based).

Is the above letter sequence correct?

 

[Keala]

John_W thank you for answering. Yes the task is similar, I did try to change your program to fulfill the new mission by changing n and m but it was not that easy. The pattern is correct, that is how I want the files to be named.

Thank you