Copy Folder then rename files in new folder from list in Excel

[jimjones1958]

Hello,

I am attempting to copy a folder and then rename the files in the copied folder with a list derived from an excel workbook. In the excel workbook, column A contains the original name, and Column B contains the new name. Here is a snapshot of the naming "from" and "to" cells:

2021-04-19_16-41-33.png

This code that I have so far just copies the folder. I need to now rename the files as determined by the list given above.

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Dim strPath As String
    Dim filesys
    Set filesys = CreateObject("Scripting.FileSystemObject")
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    ' Set fldr = Nothing
    strPath = Left(sItem, InStrRev(sItem, "\") - 1)
    strPath = filesys.BuildPath(strPath, "Copy1")
    ' Create the folder "Copy1"
    filesys.CopyFolder sItem, strPath
End Function

[cubangt]

Here is some logic found elsewhere, step thru the code and make sure that you are passing the correct "path"/ "filename" into the line that names

Sub RenameFile()
    Dim z As String
    Dim s As String
    Dim V As Integer
    Dim TotalRow As Integer
    
    TotalRow = ActiveSheet.UsedRange.Rows.Count
    
    For V = 1 To TotalRow
        
        ' Get value of each row in columns 1 start at row 2
        z = Cells(V + 1, 1).Value
        ' Get value of each row in columns 2 start at row 2
        s = Cells(V + 1, 2).Value
        
        Dim sOldPathName As String
        sOldPathName = z
        On Error Resume Next
        Name sOldPathName As s
        
    Next V
    
    MsgBox "Congratulations! You have successfully renamed all the files"
    
End Sub

[jimjones1958]

Hello,

Thanks for your response.

Where do I insert your code?

I tried the code below but could not get it to work:

Function GetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Dim strPath As String
    Dim filesys
    Set filesys = CreateObject("Scripting.FileSystemObject")
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    ' Set fldr = Nothing
    strPath = Left(sItem, InStrRev(sItem, "\") - 1)
    strPath = filesys.BuildPath(strPath, "Copy1")
    ' Create the folder "Copy1"
    filesys.CopyFolder sItem, strPath
End Function

Sub RenameFile()
    Dim z As String
    Dim s As String
    Dim V As Integer
    Dim TotalRow As Integer
    
    TotalRow = ActiveSheet.UsedRange.Rows.Count
    
    For V = 1 To TotalRow
        
        ' Get value of each row in columns 1 start at row 2
        z = Cells(V + 1, 1).Value
        ' Get value of each row in columns 2 start at row 2
        s = Cells(V + 1, 2).Value
        
        Dim sOldPathName As String
        sOldPathName = z
        On Error Resume Next
        Name sOldPathName As s
        
    Next V
    
    MsgBox "Congratulations! You have successfully renamed all the files"
    
End Sub

[cubangt]

Depends when you want to rename the files..
You can call it separately and let it rename the files based on a folder
Or you can call the rename right after your copy line, but you would need to update the routine to accept the path and file name for it to work.

filesys.CopyFolder sItem, strPath

Call RenameFile()