How to rename image file names in a folder from an excel worksheet

[PatMyer]

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 to the updated name. I have tried creating a batch file from Notepad and saving/executing from the folder but that did not work. Is there a Marco or Command Prompt code that can be used?

 

[mikenelena]

Hi PayMyer,

Here is my suggestion for how to go about this. I would probably do this in 2 separate subroutines. In the first sub, gather the names of all .png files from a particular folder and list them in a column. My code uses column A. Then, you can list your new names in another column (my code uses column B), and run the 2nd subroutine which will rename files with the name listed in column B.

Sub GetFiles()

Dim fso As Object
Dim folder As Object
Dim file As Object
Dim rowNum As Long

rowNum = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Users\Elena\Desktop\png rename") 'Rename this with the folder that contains your .png files

For Each file In folder.Files
    If Right(file.Name, 4) = ".png" Then
        Cells(rowNum + 1, 1).Value = file.Name
        rowNum = rowNum + 1
    End If
Next

Set fso = Nothing
Set folder = Nothing
Set file = Nothing

End Sub

And here is the 2nd subroutine that will handle the renaming:

Sub RenameFiles()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim oldName As String
    Dim newName As String
    Dim filePath As String
    Dim i As Long

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\Users\Elena\Desktop\png rename") ' Replace with your folder path

    For Each file In folder.Files
        oldName = file.Name
        For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            If oldName = Cells(i, 1).Value Then
                newName = Cells(i, 2).Value
                filePath = folder.Path & "\" & newName
                fso.MoveFile file.Path, filePath
                Exit For
            End If
        Next i
    Next file

    Set fso = Nothing
    Set folder = Nothing
    Set file = Nothing
End Sub

Let us know how it goes for you.

Have a nice weekend,
...Mike

 

[alch88vdet]

It worked perfectly. Thank you very much