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.
- Solution
- Code
- Instructions
- Example Situations and Solutions
- If file does not existing in the source location
- If file is exists in the source location
- If file is already exists in the destination folder
- Code
- Example File to Download
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Specify the required file and folder locations
- Press F5 to execute the code
- 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.
Case 2: If file is exists in the source location
VBA code should copy the source file to the destination folder.
Here is the output after executing the macro.
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.
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.
Code doesnt work.
Hi Siemon,
Could you please explain where exactly your getting the issue.
Thanks-PNRao!
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.
Hi Albert,
This feature is available in our add-in, please download and use it – It is free.
Thanks-PNRao!
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 :)
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!
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 ! :D
Thanks for your effort into easing affairs of humans on earth !
Have a good day !
how to copy folders from one path to another depending upon a list in excel
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.
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.
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
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
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.?
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
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 Work1”
‘Change to match the destination folder path
sDFolder = “D:Dec Work2”
But its not able to find my file . Can you please tell me where i am doing mistake ?
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.
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 driveLee’s Hard DriveMy DocumentsWBD052U_PRINT01*.txt)
Column E has Destination, (C:UsersLeeDocumentsWork 1. 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
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
Above Examples Are Helps to cope a Single File, How to move 3 or more file by This?
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.
I am also facing the same issue. Pls help…
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.
Thank you, thank you, thank you, Very Helpful!!!
Hi
Thanks for sharing useful code. I would like to copy the files from one folder to another with specific extension. As I am avoiding garbage to be copied, and I would like to copy only data files for backup.
Would you please help me.
Thanks in advance
Rajesh Bawkar,
Hi,
I am a completee beginner with VBA.
Is it possible to use wildcard with sFile?
I get following to work just fine:
format = Sheets(“Blad1”).Range(“A4”)
namn = ActiveCell.Offset(i, 0)
‘This is Your File Name which you want to Copy
sFile = namn & format
But i would like to be able to move a file that starts with (name in cell) and then regardless of the suffix will be copied..
Something like
format = Sheets(“Blad1”).Range(“A4”)
namn = ActiveCell.Offset(i, 0)
‘This is Your File Name which you want to Copy
sFile = namn & “*” & format
But it doesn’t work. Any idea how to make it work?
***FIX***
He/She left out some back-slashes.
Put a backslash at the end of both the source and destination addresses.
That should fix it.
Copy Files from One Location to Another using Excel VBA. Will help you while handling with the file operations.
this code is very use full for me thank you very much. is it possible during running of this code that a input box appears through which we can rename the new created file
Hi..
I am a beginner, as per your example file I just change path & file name but when run the macro showing specified file not found.
Can u please suggest me where is the wrong. for you reference.
‘In this Example I am Copying the File From “D:AAABBBTEST-1” Folder to “D:New folderCCCTEST-2” 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 = “CHECK.dwg”
‘Change to match the source folder path
sSFolder = “D:AAABBBTEST-1”
‘Change to match the destination folder path
sDFolder = “D:New folderCCCTEST-2”
‘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
Possibly this reply are far too late for the question, but I will just reply for any others references in future,
Your folder path should end with “,
eg:- sSFolder = “D:Dec Work1” not sSFolder = “D:Dec Work1”
This should work excellently
Hello,
I have one question, can we copy folder instead of file, and can we rename it with current date of back up.
Thanks in advance
Hi,
This code is very useful and saves a lot of time.
But, it always shows “Specified File Not Found” in my system.
Even though the file exists.
Please help
Hi Sir,
The code worked really good,
What if the i want to multiple files to multiple folders respectively
Hi, if I wanted a directory name to be called what is in a specific cell like (A1=2017) and the file name to be created to incorporate that same value but in a fixed name how would I do that?
Please check once that you have provided ” at the end while assigning source and Destination file path
Hi,
Is thic code cpable of copy a bulk of files from 1 folder to another based on an Excel list given?
Thanks.
Always says 2nd file not found
Please change make sure that you have both Source and Destination Files.
I would like a link to the AddIn that you mentioned above for doing a lists.
And for people who are have an issue with sourse name not found or destination not found look to your address. In each case you must have an \ on the end of both your souce path and your destination path.
Your sourse path in cell should look like “C:\Fred\” and destination path should look like “D:\Fred\”
On a side note if you change the line of “Sheets(“Main”).Range(“B6″)” and Sheets(“Main”).Range(“B7”) to Sheets(“Main”).Range(“B6”) & “\” and Sheets(“Main”).Range(“B7”) & “\” it will add the rear slash automaticaly.
Thanks for the above sode.