Wednesday, 25 May 2016

How to Rename the Files in Folder

VBA Macro to Rename the Excel Files in a Folder

Sub ReNameFiles()

Dim MyObj As Object, SrcDir As String, ObjFile As Object
   
SrcDir = ("C:\Users\Reddy\Desktop\Tamatam\Test\")

Set MyObj = CreateObject("Scripting.FileSystemObject")

 For Each ObjFile In MyObj.GetFolder(SrcDir).Files

   'Rename only the Excel Files   
        If InStr(ObjFile.Name, ".xls") = 0 Then Exit For
        
        If InStr(1, ObjFile.Name, "Dashboard") > 0 Then
                Pos = InStr(5, ObjFile.Name, "_")
            Else
                Pos = InStr(1, ObjFile.Name, "-")
        End If
        
        FilName = Left(ObjFile.Name, Pos - 1)
     
        ID = Trim(StrReverse(Right(Left(StrReverse(ObjFile.Name), 10), 5)))
        FY= "FY16"
        
        New_Name = FilName & "_" & FY & " - " & ID & ".xlsx"

   'Renaming the File
        ObjFile.Name = New_Name
    
  Next ObjFile    

End Sub

---------------------------------------------------------------------------------------------
Output  :

after renaming the files :




No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts