Copy and Rename files

[TaskMaster]

Hi all,

I have the following vba that works as expected where it moves files from one location to another. I would like to build on this by renaming these in the destination folder from .txt to .csv is this possible?

Public Sub Copy_Files2()

    Dim Folder As String
    Dim Dest As String
    Dim LastMonth As Date
    
    LastMonth = DateSerial(Year(Date), (Month(Date) - 1), Day(Date))
    Folder = "\\users\TM\Desktop\Test\"
    Dest = "\\users\TM\Desktop\Test\" & Format(LastMonth, "mm. ") & Format(LastMonth, "mmmm") & "\Data\Reports"
    
    Check_and_Copy_Files Folder & "330016.txt", Dest
    
    Check_and_Copy_Files Folder & "330008.txt", Dest
    
    Check_and_Copy_Files Folder & "330009.txt", Dest
    
    Check_and_Copy_Files Folder & "330010.txt", Dest
    
    Check_and_Copy_Files Folder & "330011.txt", Dest
    
    Check_and_Copy_Files Folder & "330013.txt", Dest
    
    Check_and_Copy_Files Folder & "330014.txt", Dest
    
    Check_and_Copy_Files Folder & "334008.txt", Dest

End Sub


Private Sub Check_and_Copy_Files(sourceFolderMatchFiles As String, destFolder As String)

    Static FSO As Object
    Dim FSfile As Object
    Dim sourceFolder As String, matchFiles As String
    Dim p As Long, n As Long
    
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
   
    If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
    p = InStrRev(sourceFolderMatchFiles, "\")
    sourceFolder = Left(sourceFolderMatchFiles, p)
    matchFiles = Mid(sourceFolderMatchFiles, p + 1)
    
    n = 0
    For Each FSfile In FSO.GetFolder(sourceFolder).Files
        If LCase(FSfile.Name) Like LCase(matchFiles) Then n = n + 1
    Next
    
    If n = 0 Then
        MsgBox "There are no files in " & sourceFolder & " matching " & matchFiles & ", therefore no files copied to " & destFolder, vbInformation, "No files copied"
    Else
        For Each FSfile In FSO.GetFolder(sourceFolder).Files
            If LCase(FSfile.Name) Like LCase(matchFiles) Then
                If FSO.FileExists(destFolder & FSfile.Name) = False Then
                    FSfile.Copy destFolder
                End If
            End If
        Next
    End If
    
End Sub

 

[Domenic]

To rename the copied file, simply add the new file name in the destination path.

Hope this helps!

 

[TaskMaster]

Hi Dom, got it - thank you

 

[Domenic]

You're very welcome, glad I could help, cheers!