Copy Rename files in excel

In this blog article I will demonstrate file copying techniques using excel visual basic for applications.

The code below is tested in Excel 2010, Windows 7.

Copy a file

The following macro copies a file. The file name is in cell B6, the source directory is in cell B3. Destination directory is entered in cell D3.
copyfile

Sub CopyFile()
Dim src As String, dst As String, fl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & fl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & fl
    End If
On Error GoTo 0
End Sub

Copy and rename a file

This macro copies a file specified in cell B6 and renames the file using the value in cell D6. Cell B3 and D3 contain the source and destination paths.
copyrenamefilevba

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

Rename a file

This example renames a file.
rename-file-vba

Sub RenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Folder
src = Range("B3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")
On Error Resume Next
    Name src & "\" & fl As src & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

List files in a folder

The following macro lists files in a specified directory.
listfilesfoldervba

Sub ListFilesinFolder()
Dim Value As String
Dim strt As Range
Set strt = Range("B6")
Value = Dir(Range("B3"), &H1F)
Do Until Value = ""
If Value <> "." And Value <> ".." Then
    strt = Value
    Set strt = strt.Offset(1, 0)
End If
Value = Dir
Loop
End Sub

Excel is great tool for copying and renaming hundreds or thousands of files very quickly. If you are looking for a macro that copies and or renames multiple files, contact me.

List files in a folders and sub-folders

List files in a folder and subfolders

Download excel *.xlsm file

Copy rename files (excel vba).xlsm