Wednesday, May 25, 2016

How to Rename the Files in Folder using VBA Macro

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 :


#--------------------------------------------------------------Thanks--------------------------------------------------------------#

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog