Forum Discussion

PatMyer's avatar
PatMyer
Copper Contributor
Feb 24, 2023

How to rename file names from an excel cell

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
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Feb 24, 2023

    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

     

PatMyer's avatar
PatMyer
Copper Contributor
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.
HansVogelaar's avatar
HansVogelaar
MVP
Feb 24, 2023

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.

  • PatMyer's avatar
    PatMyer
    Copper Contributor
    Feb 24, 2023

    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.  

     

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 24, 2023

      PatMyer 

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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 24, 2023

      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

       

      • PatMyer's avatar
        PatMyer
        Copper Contributor
        Feb 24, 2023
        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.

Resources