Batch append file names based on fields in Excel

A

AnOriginal

New Member
Joined
Feb 14, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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.
VBA Code: 
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
 
Upvote 0
Solution
OMG! Simply amazing! I appreciate you getting back to me so quickly. It all worked perfectly. Thank you so much! You're a genius!
 
Upvote 0
AnOriginal said:
OMG! Simply amazing! I appreciate you getting back to me so quickly. It all worked perfectly. Thank you so much! You're a genius!
Click to expand...
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.
 
Upvote 0
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?
 

Attachments

  • MacroError.jpg
    MacroError.jpg
    120 KB · Views: 1
Upvote 0
Oops, the path was actually:

folderPath = "/Users/anlefevre/Pictures/PYBS2024/PNGs" '<----- CHANGE THIS PATH
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
542
MARK858
MARK858
G
  • Question
VBA Save File Based On Filtered Change In Range
Replies
4
Views
515
GoHeels
G
S
  • Question
CSV export to remove column
Replies
1
Views
234
StellarRumble59
S
JeffGrant
  • Question
Running cmd batch file repeatedly...
Replies
1
Views
427
offthelip
O
J
  • Question
Automatically setting filters based upon user selection criteria
Replies
1
Views
200
John_w
J

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

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