The old file names are in Column A and new file na

Question

I have an excel file that has two columns, column A & Column B.

The column A is "Path and filenames that had been selected", which has path of each file name in a network directory:

\\xxx\yyyy\gggg\ooo.pdf
\\xxx\yyyy\gggg\ogh.pdf
\\xxx\yyyy\gggg\pjo.pdf

The column B has "new file names", which are supposed to replace old file names (ooo.pdf, ogh.pdf, pjo.pdf):

fff.pdf
fgh.pdf
hjk.pdf

I have a code that is supposed to rename the old file names with new file names, but somehow it is not working effectively. PLease let me know what is wrong with the below code :

Sub RenameFiles()
Dim xDir As String
Dim xFile As String
Dim xRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
If .Show = -1 Then
    xDir = .SelectedItems(1)
    xFile = Dir(xDir & Application.PathSeparator & "*")
    Do Until xFile = ""
        xRow = 0
        On Error Resume Next
        xRow = Application.Match(xFile, Range("A:A"), 0)
        If xRow > 0 Then
            Name xDir & Application.PathSeparator & xFile As _
            xDir & Application.PathSeparator & Cells(xRow, "B").Value
        End If
        xFile = Dir
    Loop
End If
End With
End Sub

Please let me know the VBA code that can replace the old file name with the new file name in the directory.

Answer 1

You could use FileSystemObject:

Sub Iterator()

    'Tools -> References -> Microsoft Scripting Runtime
    Dim fso As New FileSystemObject
    Dim fl As File
    Dim r As Long

    For r = 1 To 3
        Set fl = fso.GetFile(Cells(r, "A")) 'Path is taken from column "A"
        fl.Name = Cells(r, "B") 'Rename with name taken from column B
    Next

End Sub