Copy Files from One Location to Another folder or directory in Excel VBA

Home/Excel VBA/Copy Files from One Location to Another folder or directory in Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference
By |April 28th, 2013|Excel VBA|22 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

22 Comments

  1. Siemon March 24, 2014 at 6:58 PM - Reply

    Code doesnt work.

    • PNRao March 29, 2014 at 12:26 PM - Reply

      Hi Siemon,
      Could you please explain where exactly your getting the issue.
      Thanks-PNRao!

  2. Albert April 23, 2014 at 2:36 AM - Reply

    The last example works for me, I would however like it if the search would look in the source folder and all sub folders. Then I would want it to copy a list of files, this list would be found in the Excel file, is this possible? Thanks.

    • PNRao April 24, 2014 at 8:57 PM - Reply

      Hi Albert,

      This feature is available in our add-in, please download and use it – It is free.

      Thanks-PNRao!

  3. Risha August 14, 2014 at 8:17 PM - Reply

    Hi,
    Thanks for the code, its very helpful.
    I am looking for a similar code to sbCopyingAFileReadFromSheet, where instead of having one file only, there is a list of files. I tried using a Do Loop, but keep getting an “Object doesn’t support this property or method.
    Please help 🙂

    • PNRao August 17, 2014 at 11:39 AM - Reply

      Hi Richa,

      It should work with loops. Could you please post that code here, so that I can have look and correct if there are any mistakes.

      Thanks-PNRao!

  4. Akhi September 18, 2014 at 2:41 PM - Reply

    Mr. PNRao,

    Your tutorials have helped a lot, I am curious to know if this could also be used for a list of files (From a list in excel)
    I’ve uploaded an excel sheet here http://www47.zippyshare.com/v/70981199/file.html
    Kindly let me know or if you could email me back on akhithinker@gmail.com it would be just AWESOME ! 😀

    Thanks for your effort into easing affairs of humans on earth !
    Have a good day !

  5. Dhananjay November 17, 2014 at 2:40 PM - Reply

    how to copy folders from one path to another depending upon a list in excel

  6. Roman V April 7, 2015 at 6:48 PM - Reply

    How can I make this run daily based on a having the vba recognize the day and interact with the date in the file name as it gets changed.

  7. Rudi May 20, 2015 at 1:53 PM - Reply

    Hi, thank you very much for sharing this useful code.

    Please advice on a different scenario, how will the code be altered if I want to Override the Destination File?

    Thx Rudi.

  8. Steve June 18, 2015 at 5:33 AM - Reply

    Hi I had an issue with this code. I’m getting a “Permission Denied” error. I am trying to transfer the file in my work environment, but i have the credentials to access all of the files normally (i.e. without VBA). Any help appreciated.

    Thanks
    Steve

    • Yash November 4, 2016 at 9:24 AM - Reply

      I am also facing the same issue. Pls help…

  9. DK July 15, 2015 at 12:03 AM - Reply

    Hi siemon!

    i’ not yet a pro in Macro but your work is a big help for me. i got an idea from your work and applied from the combined results of my research from the net. However, I’m really having a hard time on figuring it out to make a copy of a file to many copies and rename them based on the list. here is the the code so far. i indicated a note on the code where iim having a problem. can you also please email me in this address dickcel_2000@yahoo.com if you figured it out. really need it badly. please help me.

    Sub CopyandRenameFile()
    Dim src As String, dst As String, fl As String
    Dim rfl As String
    ‘Source directory
    src = Range(“B3″)
    ‘Destination directory
    dst = Range(“b9″)
    ‘File name
    fl = Range(“B12″)

    ‘ —->RENAME FILE AND I WANT TO HAVE A LIST. I TRIED (“D1:D10″) BUT DIDN’T WORK.
    rfl = Range(“d1″)

    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

  10. Zoaib zaidi July 26, 2015 at 1:43 PM - Reply

    I am happy to find such a helpfull website. I want to know how if i want to copy many more files all together in one go.?

  11. Cheam Reaksa November 6, 2015 at 11:22 PM - Reply

    Hi, siemon

    Please, I would like to need your help on my VBA project. I want to copy data from multi workbook(A B C D) to main workbook(E) but from day to day my data was changed. I want my data are order WB A B C D, how VBA can help me.

    Best regard,

    C Reaksa

  12. shaif December 8, 2015 at 9:52 AM - Reply

    Hi Guys ,
    Quick question , i am facing issue within this code ,
    I changed my file type and path of file in this code and script is notable to find my file . Following are the changes

    ‘This is Your File Name which you want to Copy
    sFile = “Sample.xlsx” (changed to xlsx as my file is sample.xlsx)

    ‘Change to match the source folder path
    sSFolder = “D:\Dec Work\1”

    ‘Change to match the destination folder path
    sDFolder = “D:\Dec Work\2”

    But its not able to find my file . Can you please tell me where i am doing mistake ?

  13. Tari February 3, 2016 at 9:01 PM - Reply

    Hello Guys,

    Can you set the source folder path by choosing the file and similarly let the user browser to destination folder to copy the chosen file?

    Thanks for you help.

  14. Lee Pettersson May 11, 2016 at 12:11 PM - Reply

    Hi
    I am hoping you can help.
    I have the following Code. I am attempting to move all our daily files from a listing in excel.
    Column D has the Source, (D:\Hard drive\Lee’s Hard Drive\My Documents\WBD052U_PRINT01*.txt)
    Column E has Destination, (C:\Users\Lee\Documents\Work\01. WBD52U\)

    When I run the macro I get the following error
    Run time error 13. Type mismatch.

    If I just do 1 line i.e. FromPath Range(D5:D5) ToPath Range (E5:E5) this works perfectly.

    As soon as I specify greater range it does not work.
    Thank you I really need some help.

    Sub Move_Daily_Files()
    Dim FSO, MyFile
    Dim FromPath As String
    Dim ToPath As String
    FromPath = ActiveSheet.Range(“D5:D7”)
    ToPath = ActiveSheet.Range(“E5:E7”)
    Set FSO = CreateObject(“Scripting.FileSystemObject”)
    FSO.CopyFile (FromPath), ToPath, True
    MsgBox “File Copied to Destination Folder Successfully”, vbInformation, “Done!”
    FSO.CopyFile Source:=FromPath, Destination:=ToPath
    End Sub

  15. Manish Gupta June 14, 2016 at 10:10 AM - Reply

    Hello,
    I need help with regards to a code.. where in I have a list of file names (excluding their extension) in column A2:A251. There are files with these names in a given folder. Every time I run the Macro, I want any one of the action to take place.
    a) either the file names are changes with current filename+ yesterday’s date (last working day’s date) and moved to a specific folder (folder will not change)
    OR
    b) a new folder is created with yesterday’s date (last working date) every time the macro is run and move those files into the folder

    finally delete the files from the source folder

  16. Chirpi July 1, 2016 at 4:41 PM - Reply

    Above Examples Are Helps to cope a Single File, How to move 3 or more file by This?

  17. sahadev Khetani July 15, 2016 at 12:27 PM - Reply

    Hi..
    I have a question like,
    I want to search a particular file in the source folder and then copy that file and paste it in the destination folder is that possible?
    If possible then please tell me how.

  18. Abhidarshi June 21, 2017 at 12:34 AM - Reply

    Hi Thanks for sharing the code.

    I personally find it very useful but when i tried to run it i am getting a mgs the “Specified File Not Found”. what should i do to correct he error.

    Thanks in advance.

Leave A Comment