Rename files from old name in column C and new name in column D

[KlausW]

Hello everyone

Anyone who can help, I use this VBA code to rename all files in a folder / subfolder.
Folder in Cell H1
But now I would like the old name to be in column C2 and down and the new name in column D2 and down. The new files must have the same file format as before it was renamed.
Anyone who can help?
Any help will be appreciated

Best Regards
Klaus W

Sub Rename_Files()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject

Dim fo As Folder

Dim f As File

Dim new_name As String

Set fo = fso.GetFolder(sh.Range("H1").Value)

For Each f In fo.Files

new_name = Application.VLookup(f.Name, sh.Range("A:c"), 3, 0)

f.Name = new_name

Next

MsgBox "All files renamed successfully!"

End Sub

 

[Skyybot]

Assuming that the posted code works, here is a small edit to your code

Sub Rename_Files()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject

Dim fo As Folder

Dim f As File

Dim new_name As String
Dim old_name As String

Set fo = fso.GetFolder(sh.Range("H1").Value)

For Each f In fo.Files

old_name = sh.Range("H1").Value

new_name = Application.VLookup(old_name, sht.Range("A:C"),3,0)

f.Name = new_name

Next

MsgBox "All files renamed successfully!"

End Sub

 

[Domenic]

With the old name in Column C and the new one in Column D, try...

new_name = Application.VLookup(f.Name, sh.Range("C:D"), 2, 0)

or

    With Application
        new_name = .Index(sh.Range("D:D"), .Match(f.Name, sh.Range("C:C"), 0))
    End With

Hope this hleps!

 

[KlausW]

With the old name in Column C and the new one in Column D, try...

new_name = Application.VLookup(f.Name, sh.Range("C:D"), 2, 0)

or

    With Application
        new_name = .Index(sh.Range("D:D"), .Match(f.Name, sh.Range("C:C"), 0))
    End With

Hope this hleps!

Hi Domenic, thank you, it should be just as it should be. Good day greetings from Denmark Klaus W

 

[Domenic]

Hi Klaus, you're very welcome, cheers!