Forum Discussion
How to rename file names from an excel cell
- Feb 24, 2023
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
You marked your reply as the best response, so I take it your problem has been solved.
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.
- HansVogelaarFeb 24, 2023MVP
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.
- PatMyerFeb 24, 2023Copper Contributor
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.
- HansVogelaarFeb 24, 2023MVP
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