Renaming files in a folder using VBA

I offloaded close to 400 pictures from my wife's camera, when she decided she didn't want them removed. However, the built-in Windows XP importer had already deleted the photos from the camera's 4 GB MS Pro Duo memory card (my mistake). And of course it had kindly renamed all of the photos as well.

When I tried to put the photos back on, the camera wouldn't recognize them, because the filename had changed. Changing 400 filenames manually wasn't something I was interested in doing, so I turned to VBA to solve this problem.

Here's some code that will loop through a folder and rename all the files. It's a hard-coded, quick-n-dirty routine that you'll need to adapt. The filenames all have to be in the following format to be recognized by the camera: DSCnnnnn.JPG. At some point in the future I'll function-ize this to make it more flexible.

Sub ChangeFilename()

Const FILEPATH As String = _
"C:\Documents and Settings\Jimmy Pena\Desktop\camera photos\"

Dim strfile As String
Dim filenum As String

strfile = Dir(FILEPATH)

Do While strfile <> ""
  Debug.Print strfile
  If Right$(strfile, 3) = "jpg" Then
    filenum = Mid$(strfile, Len(strfile) - 6, 3)
    Name FILEPATH & strfile As FILEPATH & "DSC00" & filenum & ".JPG"
  End If

  strfile = Dir

Loop

End Sub

A Do Loop is employed after grabbing the first filename in the folder with the Dir function. There was a mix of jpg and mpg files from the camera in the folder, so I focused on the jpg files first. Windows renamed the files, but left a three digit number in the filename, which I assigned to the filenum variable. Then I used the "NAME x As y" method to change the filename to a legal one (for the camera, anyway). The loop continues until all jpg files are renamed.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 10 Comment(s) on Renaming files in a folder using VBA:

  1. Thanks a ton, JP. I was stuck in a similar situation also. God!! you saved me from lots and lots of hassel.

    Sumit.

  2. Hi JP,

    I wanted to print all the files in a particular folder which includes .XLs, .Doc & .PDF files. I have used your above code which is able to identify all the files, but can you help me out in finding the way to print them as well without the user intervention.

    Thanks,
    Mohit

    • Mohit,

      For Word .docs, check out the Application.PrintOut method. For Excel .xls files, check out Worksheet.PrintOut method. For .PDF files, see the CAcroAVDoc.PrintPages method. I found some sample code for printing PDFs here:

      Print a PDF file using VBA

  3. Thank you. Your code helped me with a problem I had for I needed to randomly rename my picture files for a slideshow I was creating for my son's football team.

    Sub RandomlyRenamePictureFiles()
    
        Const sFilePath As String = _
        "C:\Documents and Settings\Sabbath\Desktop\Football\2009\CD\"
    
        Dim sFile As String
        Dim sFileNumber As String
    
        sFile = Dir(sFilePath)
    
        Do While sFile <> ""
            If Right$(sFile, 3) = "jpg" Then
            sFileNumber = Int((9999 - 1000 + 1) * Rnd + 1000)
            Debug.Print sFile & " = " & sFileNumber & ".JPG"
            Name sFilePath & sFile As sFilePath & sFileNumber & ".JPG"
        End If
    
        sFile = Dir
    
        Loop
    
        MsgBox "File renaming completed."
    
    End Sub
    
  4. Hi JP. Very interesting, exactly what I am trying to solve. Please I need help with this project. I have let's say 10 pdf files with whatever names. I want to rename them using a macro, preferably excel because an excel spreadsheet will have the new names, using formulas, functions, the excel spreadsheet will have on sheet one A1:A10 the new names; those names need to be incorporated in the macro that would change the pdf files names, without opening those files. Do you think that it is possible? Or what other why, but using as sourse for new names excel; I find it easier to stracture the name base on some info comun to all 10 files and some particular; lets say the name would look like this

    Client A EIAI 10102 2010.08.26.pdf
    Client B LTL 10132 2010.05.13.pdf
    Client C ALFB 10233 2009.11.30.pdf

    Thank you so much for your help.

    Vera

    • What I would do is use a directory loop to rename each file, then grab the next name from the range and rename the file. But you'll need some way to match up the old filenames with the new ones.

  5. Hi JP,
    Thanks a Lot for the code. This Helped me in providing a solution for my current issue.
    Thanks,
    Kamalan

  6. Travis H. writes:

    Thanks for the code, this is a good start for a guy who is really struggling. I'm try to bring together file of photos from two computers. One is YYYY_MM_DD and the other is YYYY-MM-DD! So close & yet different, so they won't merge (the file names themselves are fine, I want the directories to rename & then will merge onto one machine so I have them all in one spot). I wanted to just use a macro to rename them, maybe with a find & replace type operation. Any suggestions would be much appreciated.

    • I'm not clear on why the procedure I've provided isn't adequate to rename all the image files you have on each computer.

      If the files on one computer are named YYYY_MM_DD just loop through the folder and name them whatever you like. On the other computer (where the files are named YYY-MM-DD) do the same. Or am I not understanding the situation?

  7. Thanks you very much JP. I just renamed over 3000 PDF's! What at time saver.
    Quick question, this line► strfile = Dir
    It appears to increment to the next file in the directory, how does this work?

This article is closed to any future comments.
learn excel dashboards