I have a macro that is renaming a group of files based on a given name. Im trying to get the new list of new file paths created to have a macro create a new CSV file with them for use in another application. here is the code im using
any help would be grateful thanks in advance
VBA Code:
Sub Rename_Files()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Home")
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:B"), 2, 0)
If new_name = "" Then 'do nothing
Else
f.Name = new_name
End If
Next