August 20, 2017

Copy Specific Files or All Files from Source Folder to Destination Folder with VBA

How to copy specific files or all files from source folder to destination folder with VBA. In this solution we have used the File System Object or FSO for short.

What is FSO? It offers an easy object based model to access the file system on our computer. First we need to create an instance of FileSystemObject in VBA as shown in the code below. Now using FSO we can generate files, read files, copy or move files, delete files, check if folder exists and loop though folders and sub-folders. Below are given some interesting links for further study.

Watch the video below:

 

Watch this video on YouTube.

The following VBA code will copy all Excel files or files that you specify from a source folder to the specified destination folder:

Sub copy_specific_files_in_folder()
Dim FSO As Object
Dim sourcePath As String
Dim destinationPath As String
Dim fileExtn As String

sourcePath = “C:\exceltrainingvideos”
destinationPath = “C:\Users\takyar\test100\”

fileExtn = “*.xl*”

If Right(sourcePath, 1) <> “\” Then
sourcePath = sourcePath & “\”
End If

Set FSO = CreateObject(“scripting.filesystemobject”)

If FSO.FolderExists(sourcePath) = False Then
MsgBox destinationPath & ” does not exist”
Exit Sub
End If

If FSO.FolderExists(destinationPath) = False Then
MsgBox sourcePath & ” does not exist”
Exit Sub
End If

FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath
MsgBox “Your files have been copied from ” & sourcePath & ” to ” & destinationPath

End Sub

 

Note: before you start using the File Object Systen or FSO in VBA you need to activate a reference of Microsoft Scripting Runtime Library as shown in the images below:

  1. Click on Tools in the Microsoft Visual Basic for Application Windows. Select References…
Reference to FSO
Reference to FSO

2. Activate Microsoft Scripting Runtime Library

Activate Microsoft Scriptung Runtime Library
Activate Microsoft Scripting Runtime Library

 

Further reading:

FileSystemObject in VBA – Explained

Accessing Files with FileSystemObject

 

6 thoughts on “Copy Specific Files or All Files from Source Folder to Destination Folder with VBA

  1. Hi,

    Thanks for the VBA code ,it works.
    Can you please provide the code extension so that it can help in searching in the subfolders also which are there in the source path

  2. Hi,
    I need a VBA, PLEASE help an support,

    I have a few list of folder’s name which I want in a excel.
    I want to pick the same in multiple folders…
    So that it took more time to find out using search tab,
    So request you help me to get the same folders which i want to another folder using VBA script…….

    If anything need please let me know

  3. I SOOOO need your help!! I have MULTIPLE files that have 69,000+ images in all.. I have figured out how to create the different folders for each image, but I can’t figure out how to move all of the files to their corresponding folders.

    Example…… I have one LARGE folder with filled with pdfs, msg, doc and jpeg…. And I am trying to move them into folders that they belong to. There may be one file that goes into 7+ different subfolders. So… Column A is the 123456.pdf or jpg or msg…. file. Column B is the FOLDER I want them to go in. Column C is the Source File where all of these images are saved. Column D is the Destination I want them to go. Keep in mind, I have 1500 different subfolders I want to move these files to and one file may go to more than one folder. Can you PLEASE help me?

Comments are closed.