Batch append file names based on fields in Excel

[AnOriginal]

Hello.

Is there a way batch append file names based on fields within an Excel spreadsheet?

I have 770 files which all have different filenames. I would like to rename the files with fields associated with each file name by appending it with values from mulitple columns.

For example, Column H2 has a generic value SPYB-8691.png. It needs to be appended with identifying values from the following columns A2_B2_C2_D2_E2_SPYB-8691.png (team_jersey#_firstname_lastname_originalfilename.png).

Each file name is unique but the A, B, C, D and E columns should have identical values.

Is this possible to do?

 

[John_w]

My understanding is that the existing file names are in column H, starting at H2 and they should be renamed using values in columns A:E (prepend to file name).

Try this macro, after editing the folder path containing the files, where indicated.

Public Sub Rename_Files()

    Dim folderPath As String
    Dim fields As Variant
    Dim i As Long
    
    folderPath = "C:\Path\to\folder\"    '<----- CHANGE THIS PATH
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    With ActiveSheet
        fields = .Range("A1", .Cells(.Rows.Count, "H").End(xlUp)).Value
    End With
    
    For i = 2 To UBound(fields)
        If Dir(folderPath & fields(i, 8)) <> vbNullString Then
            Name folderPath & fields(i, 8) As folderPath & fields(i, 1) & "_" & fields(i, 2) & "_" & fields(i, 3) & "_" & fields(i, 4) & "_" & fields(i, 5) & "_" & fields(i, 8)
        Else
            MsgBox "Cell H" & i & " " & folderPath & fields(i, 8) & " file not found"
        End If
    Next
    
    MsgBox "Done"
    
End Sub

 

[AnOriginal]

OMG! Simply amazing! I appreciate you getting back to me so quickly. It all worked perfectly. Thank you so much! You're a genius!

 

[Peter_SSs]

OMG! Simply amazing! I appreciate you getting back to me so quickly. It all worked perfectly. Thank you so much! You're a genius!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[AnOriginal]

Hi John. I hate to bother you again. Somehow, I've forgotten how this works.

I can't seem to get the right folder path. The spreadsheet is in the same folder as all the files that need to have their filenames changed.

folderPath = "C:\Path\to\folder\" '<----- CHANGE THIS PATH

So, the only thing I changed is the path.

folderPath = "C:/Users/anlefevre/Pictures/PYBS2024/PNGs" '<----- CHANGE THIS PATH

But the macro keeps telling me the can't be found. So, I have to force quit Excel because it looks like I will get this error for every one of the 5000 file names.

What am I missing?

 

[AnOriginal]

Oops, the path was actually:

folderPath = "/Users/anlefevre/Pictures/PYBS2024/PNGs" '<----- CHANGE THIS PATH

 

[John_w]

I can't seem to get the right folder path. The spreadsheet is in the same folder as all the files that need to have their filenames changed.

In that case:

folderPath = ThisWorkbook.Path & "\"

Oops, the path was actually:

folderPath = "/Users/anlefevre/Pictures/PYBS2024/PNGs" '<----- CHANGE THIS PATH

Your path is missing the drive letter and colon at the start and folders should be separated by back slashes, not forward slashes.

 

[AnOriginal]

Thank you very much! I would have never figured that out.