Bulk rename image files based on excel spreadsheet?

Posted on 2014-03-03
1 Endorsement
Last Modified: 2014-03-08
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? :)
Question by:BasilFawlty001
  • 3
  • 2
  • 2
LVL 52

Expert Comment

by:Joe Winograd, EE MVE
ID: 39901418
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
LVL 18

Accepted Solution

Steven Harris earned 500 total points
ID: 39901443
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
    ActiveCell.FormulaR1C1 = "Path and Filenames that had been selected to Rename"
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
    End With
    ActiveCell.FormulaR1C1 = "Input New Filenames Below"
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
    End With
    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)
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

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

Author Comment

ID: 39901506
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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Assisted Solution

BasilFawlty001 earned 0 total points
ID: 39901758
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!
LVL 52

Expert Comment

by:Joe Winograd, EE MVE
ID: 39901896
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
LVL 18

Expert Comment

by:Steven Harris
ID: 39901958

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.

Author Closing Comment

ID: 39914454
My solution did the trick.  Not as slick as a built in macro but it only took an extra minute.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now