?
Solved

Bulk rename image files based on excel spreadsheet?

Posted on 2014-03-03
7
Medium Priority
?
5,001 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 55

Expert Comment

by:Joe Winograd, EE MVE 2015&2016
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 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 55

Expert Comment

by:Joe Winograd, EE MVE 2015&2016
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I originally wrote this article to compare SARDU and YUMI, but have now added Easy2Boot, since that is the one I currently use and find the easiest to create and alter.
One-stop solution for Exchange Administrators to address all MS Exchange Server issues, which is known by the name of Stellar Exchange Toolkit.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

718 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