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:
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 & “\”
Set FSO = CreateObject(“scripting.filesystemobject”)
If FSO.FolderExists(sourcePath) = False Then
MsgBox destinationPath & ” does not exist”
If FSO.FolderExists(destinationPath) = False Then
MsgBox sourcePath & ” does not exist”
FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath
MsgBox “Your files have been copied from ” & sourcePath & ” to ” & destinationPath
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:
- Click on Tools in the Microsoft Visual Basic for Application Windows. Select References…
2. Activate Microsoft Scripting Runtime Library