Bulk File Rename

I need to rename many file names to match existing names in our record system. For example, new file is "10W511-803-R1.pdf" and existing file is "SHF-10W511-803^161730106.pdf". Need to rename new to existing.

Hi there,

See if the following code does what you need:

Option Explicit


Sub RenameFiles()

    Const sWORKSHEET_NAME   As String = "list"
    Const iFIRST_ROW_NO     As Integer = 2
    Const sFOLDER_NAME      As String = "H:\Results Files"

    Const sCOLUMN__CURRENT  As String = "A"
    Const sCOLUMN__NEW      As String = "C"

    Dim sName_Current       As String
    Dim iLastRowNo          As Integer
    Dim sName_New           As String
    Dim iRowNo              As Integer
    Dim wks                 As Worksheet

    Set wks = ThisWorkbook.Worksheets(sWORKSHEET_NAME)

    With wks

        iLastRowNo = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For iRowNo = iFIRST_ROW_NO To iLastRowNo

            sName_Current = .Range(sCOLUMN__CURRENT & iRowNo).Value
            sName_New = .Range(sCOLUMN__NEW & iRowNo).Value

            If Dir$(sFOLDER_NAME & "\" & sName_Current) = sName_Current Then
                Name sFOLDER_NAME & "\" & sName_Current As sFOLDER_NAME & "\" & sName_New
            End If

        Next iRowNo

    End With

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi and welcome to ExcelForum,

How can I change the "H:\Results Files" to use the current folder

'Try changing
 Const sFOLDER_NAME      As String = "H:\Results Files"

'to
Dim sFOLDER_NAME as String
sFOLDER_NAME = ThisWorkbook.Path    'ThisWorkbook is the file that contains the code

Lewis

Works perfectly - thanks to you both for your support.