Bulk rename image files based on excel spreadsheet?

Hi there,

I've got a folder with about 4000 images in it.  The images are portraits and are named with the person's id number.  I also have a spreadsheet with the id numbers and people's names in separate columns.  I'd like to update the filenames from 123-123-4567 to John-Smith_123-123-4567.

I found this answer which is similar, but not quite what I'm after.

Any excel wizards want to tackle this? :)
BasilFawlty001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe Winograd, Fellow&MVEDeveloperCommented:
Hi Basil,
I'll see if I can modify my program to do what you want. But I'm heading offsite right now and won't be back for a few hours. I'll look at it as soon as I return. Regards, Joe
0
Steven HarrisPresidentCommented:
This first code will allow you to import the files names (as they are now) into Excel:

Sub FileNametoExcel()
    Dim fnam As Variant
    Dim b As Integer
    Dim b1 As Integer
    Dim c As Integer
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Path and Filenames that had been selected to Rename"
    Range("A1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
    End With
    Columns("A:A").EntireColumn.AutoFit
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Input New Filenames Below"
    Range("B1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
    End With
    Columns("B:B").EntireColumn.AutoFit
    
    fnam = Application.GetOpenFilename("all files (*.*), *.*", 1, _
    "Select Files to Fill Range", "Get Data", True)
    
    If TypeName(fnam) = "Boolean" And Not (IsArray(fnam)) Then Exit Sub
    
    For b = 1 To UBound(fnam)
        ActiveSheet.Cells(b + 1, 1) = fnam(b)
    Next
End Sub

Open in new window


Then you can create the naming convention you want in Column B for that file using formulas, etc.

When you are ready, you will rename each file in Column A with the name you placed in Column B, using the following:

Sub RenameFile()
    Dim z As String
    Dim s As String
    Dim V As Integer
    Dim TotalRow As Integer
    
    TotalRow = ActiveSheet.UsedRange.Rows.Count
    
    For V = 1 To TotalRow
        
        z = Cells(V + 1, 1).Value
        s = Cells(V + 1, 2).Value
        
        Dim sOldPathName As String
        sOldPathName = z
        On Error Resume Next
        Name sOldPathName As s
        
    Next V
    MsgBox "Congratulations! You have successfully renamed all the files"
End Sub

Open in new window


Notes:
1. Test with example data;
2. Include the full file Name as shown in Column A;
3. Make sure to include the file extension (.jpg, .gif, etc.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BasilFawlty001Author Commented:
Thanks for the advice.

Joe,  I'll wait for your modified program before I take a look at the other answers.  I'm not in a big rush.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

BasilFawlty001Author Commented:
I actually found a quick way to do it.  I just created a formula to concatenate the fields.

=CONCATENATE("ren ",CHAR(34),A1,"-P13",".jpg",CHAR(34)," ",CHAR(34),B1," ",A1,"-P13.jpg",CHAR(34))

Which created this: ren "050-014-0017-P13.jpg" "John Smith 050-014-0017-P13.jpg"
 
And then copied that column into a batch file and ran it in the appropriate directory.

Thanks for the help though!
0
Joe Winograd, Fellow&MVEDeveloperCommented:
Hi Basil,
Just got back from my offsite and was going to look into it, so thanks for posting that you found a solution — will save me some time. Cheers, Joe
0
Steven HarrisPresidentCommented:
Basil,

Did you get a chance to try my suggestion?  Running as a built-in Excel Macro, you could have skipped the batch file process entirely and stayed within excel.
0
BasilFawlty001Author Commented:
My solution did the trick.  Not as slick as a built in macro but it only took an extra minute.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.