Description:

Many automation tasks required to Copy Files from One Location to Another folder or directory using Excel VBA. We will see how to copy files in this example.

Copy Files from One Location to Another using Excel VBA. Will help you while handling with the file operations.

Solution(s):

We can use CopyFile method of FileSystemObject in Excel VBA. First we will check whether file is existing in a location. Then we can copy the file if it exists to a required location.

Code:

Here is the example VBA code to copy the file from one folder to another folder in Excel.

'In this Example I am Copying the File From "C:Temp" Folder to "D:Job" Folder
Sub sbCopyingAFile()
'Declare Variables
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String
'This is Your File Name which you want to Copy
sFile = "Sample.xls"
'Change to match the source folder path
sSFolder = "C:\Temp\"
'Change to match the destination folder path
sDFolder = "D:\Job\"
'Create Object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Checking If File Is Located in the Source Folder
If Not FSO.FileExists(sSFolder & sFile) Then
MsgBox "Specified File Not Found", vbInformation, "Not Found"
'Copying If the Same File is Not Located in the Destination Folder
ElseIf Not FSO.FileExists(sDFolder & sFile) Then
FSO.CopyFile (sSFolder & sFile), sDFolder, True
MsgBox "Specified File Copied Successfully", vbInformation, "Done!"
Else
MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
End If
End Sub

Instructions:

  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Specify the required file and folder locations
  6. Press F5 to execute the code
  7. Now You should see your file is copied to specified location

Example Situations and Solutions

Here is the another Example to show you different situations while copying file from one location to another folder.

Case 1: If file does not existing in the source location

VBA Program to check if file exists or not in the specified location.

Copy Files form one location to another folder- example no input file

Case 2: If file is exists in the source location

VBA code should copy the source file to the destination folder.

Copy Files form one location to another folder- example with input file

Here is the output after executing the macro.

Copy Files form one location to another folder- example with output

Case 3: If file is already exists in the destination folder

VBA code should show message to the user as File is Already available in the destination folder.

Copy Files form one location to another folder

Code:

Here is the Example Code to explain the above situations.

'In this Example I am Copying the File From one loaction to another location
'as per the details specified in the Worksheet.
Sub sbCopyingAFileReadFromSheet()
'Declaration
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String
'This is Your File Name which you want to Copy.You can change File name at B5.
sFile = Sheets("Main").Range("B5")
'Change to match the source folder path. You can change Source Folder name at B6.
sSFolder = Sheets("Main").Range("B6")
'Change to match the destination folder path. You can change Destination Folder name at B6.
sDFolder = Sheets("Main").Range("B7")
'Create Object for File System
Set FSO = CreateObject("Scripting.FileSystemObject")
'Checking If File Is Located in the Source Folder
If Not FSO.FileExists(sSFolder & sFile) Then
MsgBox "Specified File Not Found in Source Folder", vbInformation, "Not Found"
'Copying If the Same File is Not Located in the Destination Folder
ElseIf Not FSO.FileExists(sDFolder & sFile) Then
FSO.CopyFile (sSFolder & sFile), sDFolder, True
MsgBox "Specified File Copied to Destination Folder Successfully", vbInformation, "Done!"
Else
MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
End If
End Sub

Example File:

Please Download the following example file and find the example to explain in the above situations we face while copying the file from one location to another folder.
Analysistabs – Copy Files from one location to another folder

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!