Copy Specific Files from Folder and Subfolders into Destination Folder

Last time we learnt how to use the File Object System (FSO) to copy specific Files from source Folder into Destination Folder. Today we learn how to loop through the subfolders of the source folder, find all the files in each subfolder and copy them into the destination folder with VBA quickly and easily. There are many approaches to solving this problem including creation of a customized function. But the FSO approach with a secondary macro which is called from the first macro provides an elegant solution. Watch the video below:

We can view this video also on YouTube.

Here’s the complete VBA code:
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 = “*.xlsx”

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 destinationPath & ” does not exist”
Exit Sub
End If

FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath

copy_files_from_subfolders

MsgBox “Your files have been copied from the sub-folders of ” & sourcePath & ” to ” & destinationPath

End Sub

Sub copy_files_from_subfolders()
Dim FSO As Object, fld As Object
Dim fsoFile As Object
Dim fsoFol As Object

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

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

Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set fld = FSO.GetFolder(sourcePath)
If FSO.FolderExists(fld) Then
For Each fsoFol In FSO.GetFolder(sourcePath).SubFolders
For Each fsoFile In fsoFol.Files
If Right(fsoFile, 4) = “xlsx” Then
fsoFile.Copy targetPath
End If
Next
Next
End If

End Sub

Further reading:

Accessing Files with FileSystemObject

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.