Tuesday, November 29, 2016

VBA code to Rename multiple files in a folder with a excel macro !!!

Rename multiple files from a folder is one of the craziest job. if someone is dealing with hundreds or thousands of files in a folder. To rename individual file one by one will definitely make someone crazy. As it is a monotonous task, which can lead to commit many mistakes while rename filenames individually. Moreover, there is not a single function available in excel to perform these job easily.

However, it is quit possible to automate this task using excel macro. Here is the code which will rename multiple file name from a specific folder:

Refer below input table which consist of old and new filenames.

'Force the explicit declaration of variables
Option Explicit

Sub Rename_Multiple_Files()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim path_dir As String
Dim file_name As String
Dim row_cntr As Long
Dim column_old_file As String
Dim column_new_file As String

'Assign values to string variable for old and new file name
column_old_file = "A:A"
column_new_file = "B:B"

'code to select input file folder and rename filename using Do Until loop
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
If .Show = -1 Then
    path_dir = .SelectedItems(1)
    file_name = Dir(path_dir & Application.PathSeparator & "*")
    Do Until file_name = ""
        row_cntr = 0
        On Error Resume Next
        row_cntr = Application.Match(file_name, Range(column_old_file), 0)
        If row_cntr > 0 Then
            Name path_dir & Application.PathSeparator & file_name As _
            path_dir & Application.PathSeparator & Cells(row_cntr, "B:B").Value
        End If
        file_name = Dir
End If
End With

MsgBox "All files renamed successfully", vbInformation

End Sub

No comments: