Solved

Bulk rename image files based on excel spreadsheet?

Posted on 2014-03-03
7
3,965 Views
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? :)
1
Comment
Question by:BasilFawlty001
  • 3
  • 2
  • 2
7 Comments
 
LVL 51

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
0
 
LVL 18

Accepted Solution

by:
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
    
    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
 

Author Comment

by:BasilFawlty001
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Assisted Solution

by:BasilFawlty001
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!
0
 
LVL 51

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
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39901958
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
 

Author Closing Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
If your app took Google’s lash recently, here are the 5 most likely reasons.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

11 Experts available now in Live!

Get 1:1 Help Now