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

 

[lrobbo314]

?

 

[lrobbo314]

Hey, John W. Since this turned out to be a colossal waste of my time, maybe you can salvage it. Do you have any idea why the op was getting that error? It seemed like a weird error and I didn't get anything on my test workbook.

 

[lrobbo314]

And I learned about, StrCmpLogicalW. Very cool. Much easier than using regex to pull out the numbers from the garbage formatted file names.

 

[Keala]

Irobbo314, sorry for the delay to answer. John_W help and solutions is great and awsome. As I can see in you code it only read out the content of the folder

Wf_B-55_PC-30.0_KC-5.0_180913-225013.txt
Wf_B-55_PC-5.0_KC-10.0_180913-230320.txt
Wf_B-55_PC-5.0_KC-15.0_180914-002136.txt
Wf_B-55_PC-5.0_KC-20.0_180914-013943.txt
...

But is your code dependent of the file name?

 

[John_w]

Hey, John W. Since this turned out to be a colossal waste of my time, maybe you can salvage it. Do you have any idea why the op was getting that error? It seemed like a weird error and I didn't get anything on my test workbook.

I tried your code on a set of sample files named according to Keala's convention, and it ran without errors. Therefore I suspect there were file names in the folder which didn't match the regular expression pattern.

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

The n and m (nested loops, I assume) is the wrong part to change. That sorts the file names into numerical order. The only part you need to change is the final For Next loop which renames the files.

This proved quite tricky to generate the letters in the correct sequence. It can be done with 3 nested loops, however I did it in one line using the VBA Mod function and integer division (the \ operator) and multiplication (the * operator).

Here is a test macro to generate the letter sequence:

Private Sub Generate_Letter_Sequence()

    Dim maxn As Long, n As Long, m As Long
    
    maxn = 160  'arbitary maximum
    For n = 0 To maxn
        m = (n \ 12) * 12 + (n \ 4) Mod 3 + n * 3 Mod 12
        Debug.Print n, String(m \ 26, "Z") & Chr(m Mod 26 + 65)
    Next
    
End Sub

Heres how the 3 parts of the m calculation works for different values of n:

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

(n \ 12) * 12   0 0 0 0 0 0 0  0 0 0  0  0 12 12 12 12 12 12 12 12 12 12 12 12 24 24 24 24 ...
(n \ 4) Mod 3   0 0 0 0 1 1 1  1 2 2  2  2  0  0  0  0  1  1  1  1  2  2  2  2  0  0  0  0 ...
n * 3 Mod 12    0 3 6 9 0 3 6  9 0 3  6  9  0  3  6  9  0  3  6  9  0  3  6  9  0  3  6  9 ...

m (Sum)         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 27 30 33 ...
Letter          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

The pattern is established with the first 4 letters, A D G J, which have the alphabetic index 0 3 6 9. This number sequence is repeated, starting at 1 (1 4 7 10) for the next 4 letters, and again starting at 2 (2 5 8 11) for the next 4 letters. So we have 0 3 6 9 repeating for the values 0, 1, 2 for each group of 12 letters. Then this double number sequence is repeated, starting at 12 for the next 12 letters, and again at 24 for the next 12 letters. Difficult to explain in words!

The m Mod 26 + 65 gives us the ASCII letter and m \ 26 is the number of Zs to prepend to it.

Here is the complete code:

Option Explicit

'StrCmpLogicalW function
'https://docs.microsoft.com/en-us/windows/desktop/api/shlwapi/nf-shlwapi-strcmplogicalw
'
'Compares two Unicode strings. Digits in the strings are considered as numerical content rather than text. This test is not case-sensitive.
'Returns zero if the strings are identical.
'Returns 1 if string1 has a greater value than string2.
'Returns -1 if string1 has a lesser value than string2.

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Public Declare PtrSafe Function StrCmpLogicalW Lib "shlwapi.dll" (ByVal string1 As String, ByVal string2 As String) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Public Declare Function StrCmpLogicalW Lib "shlwapi.dll" (ByVal string1 As String, ByVal string2 As String) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If


Public Sub Rename_Files_in_Folder2()

    Dim path As String, filename As String
    Dim n As Long, m As Long
    Dim files() As String, swap As String
    
    path = "C:\folder\path"               'MODIFY THIS LINE - FOLDER CONTAINING FILES TO BE RENAMED.  THE LAST CHARACTER SHOULD BE A BACK SLASH
    
    If Right(path, 1) <> "" Then path = path & ""  'THERE SHOULD BE A BACK SLASH CHARACTER BETWEEN EACH PAIR OF QUOTES
    n = 0
    filename = Dir(path & "*.*")
    Do While filename <> vbNullString
        ReDim Preserve files(n)
        files(n) = filename
        filename = Dir
        n = n + 1
    Loop
    
    If n > 0 Then
    
        'Bubble sort files array, calling StrCmpLogicalW to compare Unicode strings numerically to sort the file names in
        'the same order as File Explorer
        
        For m = 0 To UBound(files) - 1
            For n = m + 1 To UBound(files)
                If StrCmpLogicalW(StrConv(files(m), vbUnicode), StrConv(files(n), vbUnicode)) = 1 Then
                    'files(m) > files(n) so swap them
                    swap = files(n)
                    files(n) = files(m)
                    files(m) = swap
                End If
            Next
        Next
        
        For n = 0 To UBound(files)
            m = (n \ 12) * 12 + (n \ 4) Mod 3 + n * 3 Mod 12
            Name path & files(n) As path & String(m \ 26, "Z") & Chr(m Mod 26 + 65) & "_" & files(n)
            Debug.Print files(n) & " --> " & String(m \ 26, "Z") & Chr(m Mod 26 + 65) & "_" & files(n)
        Next
    
    End If
    
End Sub

Please test on a copy of your folder