How to Extract Excel Files From Multiple Folders With VBA

Updated July 20, 2017

Knowing how to extract Excel files from multiple folders using Visual Basic for applications (VBA) can save you time when you need to search your computer for Excel files to store them in one folder. VBA can be used in Microsoft Office applications such as Excel to automate routine tasks such as searching for files in folders. The best way to search for files in multiple folders is by using recursive programming. Recursive programming is used when you need a function to call itself from within the function.

Launch Microsoft Office Excel, click "Developer," and click "Visual Basic" to launch the VB Editor. Click the "Insert" menu and click "Module" to insert a new code module.

Type the following to create a new sub-procedure:

Dim col Files As New Collection

Copy and paste the following to create your variables:

Define the path where you want to copy the Excel files found:

Add the following code to call the function that will find the Excel files for extraction:

Add the following code to copy the files found to the path defined in Step 3:

Next vFile

End sub

Define the "RecursiveDir" function to search the folders and sub-folders for Excel files:

Public Function RecursiveDir(col Files As Collection, _

strFolder = TrailingSlash(strFolder)

End Function

Create the following function to add or remove the "\" from the folder paths:

Public Function TrailingSlash(strFolder As String) As String

End Function

Click inside the "extractExcelFiles" sub-procedure and click "F5" to run the program.

Things You'll Need

  • Microsoft Office
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.