Renaming files in a folder using VBA
April 20, 2009 • JP • No Comments • Rate This Article
• Links to this article
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.
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.
VBA ·
Rate This Article:
(1 votes, average: 4.00 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
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 if you want to learn more about VBA. Keep Reading »
Previous Post: Add-in Updates
Next Post: Converting month name to number
Thanks a ton, JP. I was stuck in a similar situation also. God!! you saved me from lots and lots of hassel.
Sumit.
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
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.
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