Solved

Bulk rename image files based on excel spreadsheet?

Posted on 2014-03-03
7
4,052 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 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
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
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

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

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