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 sourcePath & ” 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