web 2.0


Rename files in a folder using a specific extension

This tip shows you how to use VBA to rename several files in a specific folder. The code was originally done in Excel but can be applied in any application of the Office Suite...

IN THE WORKBOOK ENTER THE CODE

Private Sub Workbook_Open()

    fileExtension = "jpg"

    msg = "The files with extensions " & fileExtension _
      & " in the folder " & ThisWorkbook.Path & " will be renamed."
   
msg = msg & " Are you sure you wish to continue?"

    answer = MsgBox(msg, vbQuestion + vbYesNo)   

    If Not answer = vbYes Then Exit Sub

    Call renameExtensions(fileExtension)

End Sub
  IN A MODULE ENTER THE CODE

Sub renameExtensions(ByVal theExtension As String)

   filePath = ThisWorkbook.Path
  
  
Set FsoObj = CreateObject("Scripting.FileSystemObject")
  
  
If FsoObj.FolderExists(filePath) = False Then: Exit Sub

   i = 0

   Set masterFolder = FsoObj.GetFolder(filePath)

   For Each myFile In masterFolder.Files
            If Not ThisWorkbook.FullName = myFile Then

                extExit = FsoObj.getextensionname(myFile)               
                If UCase(theExtension) = UCase(extExit) Then
                    FsoObj.MoveFile myFile, "C:\" & i & "." _
                       & theExtension
                    i = i + 1
                End If               
            End If
   Next

   Set FsoObj = Nothing

   If i <> 0 Then
    
MsgBox "The files in " & ThisWorkbook.Path & " were renamed."
  
Else:
     MsgBox "No files were renamed as extension was not found."
  
End If

End Sub

 

DOWNLOAD SAMPLE FILES HERE

Tags: , , ,

Microsoft Excel | Microsoft Excel - VBA

Comments are closed