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

Recommended article

Copy a cell range whose size is likely to change from time to time (vba)

In this blog post I will demonstrate some vba copying techniques. Example 1, This example code copies cell range A1:B2 […]

Comments(0) Filed in category: Excel

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

Recommended article

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

Comments(38) Filed in category: Check-boxes, Excel, Files and folders

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

Recommended article

Identify duplicate files in excel

Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to […]

Comments(7) Filed in category: Excel, Files and folders

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

Recommended article

Search all workbooks in a folder

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. […]

Comments(80) Filed in category: Excel, Files and folders

List files in a folders and sub-folders

Excel recursive udf: List files in a folder and subfolders

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Comments(15) Filed in category: Excel, Files and folders

Download excel *.xlsm file

Copy rename files (excel vba).xlsm