SOLVED

How to rename file names from an excel cell

Copper Contributor

I have a list of .png file names exported to excel that I have modified (added an _ in a specific location in the name) and want to rename the same .png file names in the same folder I pulled from to excel.  I have tried creating a batch file from Notepad and saving/executing from the folder but that did not work. Can this be done?

 

 

PatMyer_0-1677266245387.png

 

10 Replies

@PatMyer 

Could you create a list of the original names in column A, and the corresponding modified names in column B?

@HansVogelaar 

Ok, same file names are now in Column A that are currently in the folder and what I want the .png file names changed to are in Column B (added _ after the 591_42).

By the way, this is just a sample size to test, the folder of images I want to update has over 10,000 .png files.

 

PatMyer_0-1677267388008.png

 

 

@PatMyer 

You marked your reply as the best response, so I take it your problem has been solved.

No, just didn't know what that did :)
Is there a macro or Command Prompt code that can take column A as a look up in the Folder (rename_test) and replace with column B. I am new at this and don't know the best steps for completing this task.

@PatMyer 

OK, I unmarked your reply.

In the workbook with the list of names, press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module to create a new code module.

Copy the following code into it:

Sub RenameFiles()
    ' Change the path as needed, but keep the trailing backslash
    Const sFolder = "C:\MyFiles\"
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:B" & m).Value
    On Error GoTo ErrHandler
    For r = 2 To m
        Name sFolder & v(r, 1) & ".png" As sFolder & v(r, 2) & ".png"
    Next r
    Exit Sub
ErrHandler:
    MsgBox "Failed to rename " & v(r, 1), vbInformation
    Resume Next
End Sub

Change the value of the constant sFolder at the beginning of the macro to the full path of the .png files, including the backslash \ at the end.

You might want to test the code on a folder with just a few files to see if it does what you want, before you run it on the folder with all the files.

To run the macro, click anywhere in it and press F5.

@HansVogelaar Hmmm, failed to work.  I wonder if it is because we use OneDrive at my work.  Not sure if it is finding the folder "rename_test".  I changed to "rename_test1" and got the same error so might not be locating the folder.  

 

 

PatMyer_0-1677274378924.png

 

@PatMyer 

Did the code rename any file at all, or did it fail at the first one (the one in A2)?

best response confirmed by PatMyer (Copper Contributor)
Solution

@PatMyer 

Oh wait - my bad. The values in the worksheet already include .png.

Try this version:

Sub RenameFiles()
    ' Change the path as needed, but keep the trailing backslash
    Const sFolder = "C:\MyFiles\"
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:B" & m).Value
    On Error GoTo ErrHandler
    For r = 2 To m
        Name sFolder & v(r, 1) As sFolder & v(r, 2)
    Next r
    Exit Sub
ErrHandler:
    MsgBox "Failed to rename " & v(r, 1), vbInformation
    Resume Next
End Sub

 

It worked, yes.... Thank you so much.
I wish computers were not so boring when I was in school (graduated in '81). This stuff is very interesting, just wish it just didn't fly over my head all the time when I try to learn it now.
HAVE A GREAT WEEKEND AND AGAIN, THANKS... Oh, I will "Mark as best response" now.
1 best response

Accepted Solutions
best response confirmed by PatMyer (Copper Contributor)
Solution

@PatMyer 

Oh wait - my bad. The values in the worksheet already include .png.

Try this version:

Sub RenameFiles()
    ' Change the path as needed, but keep the trailing backslash
    Const sFolder = "C:\MyFiles\"
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:B" & m).Value
    On Error GoTo ErrHandler
    For r = 2 To m
        Name sFolder & v(r, 1) As sFolder & v(r, 2)
    Next r
    Exit Sub
ErrHandler:
    MsgBox "Failed to rename " & v(r, 1), vbInformation
    Resume Next
End Sub

 

View solution in original post