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.
- Skill level:
Things you need
- Microsoft Office
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:
Dim extract Path As String Dim filename As String Dim pos As Integer
Define the path where you want to copy the Excel files found:
extract Path = "C:\Temp\"
Add the following code to call the function that will find the Excel files for extraction:
RecursiveDir col Files, "F:\moreExcelFiles\", "*.xlsx", True
Add the following code to copy the files found to the path defined in Step 3:
Dim vFile As Variant For Each vFile In col Files pos = InStrRev(vFile, "\", , vbTextCompare) fileName = Right(vFile, Len(vFile) - pos) FileCopy vFile, extract Path & filename
Define the "RecursiveDir" function to search the folders and sub-folders for Excel files:
Public Function RecursiveDir(col Files As Collection, _
strFolder As String, _ strFileSpec As String, _ bIncludeSubfolders As Boolean) Dim strTemp As String Dim col Folders As New Collection Dim vFolderName As Variant
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec) Do While strTemp <> vbNullString colFiles.Add strFolder & strTemp strTemp = Dir Loop If bIncludeSubfolders Then strTemp = Dir(strFolder, vbDirectory) Do While strTemp <> vbNullString If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then colFolders.Add strTemp End If End If strTemp = Dir Loop For Each vFolderName In col Folders Call RecursiveDir(col Files, strFolder & vFolderName, strFileSpec, True) Next vFolderName End If
Create the following function to add or remove the "\" from the folder paths:
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then If Right(strFolder, 1) = "\" Then TrailingSlash = strFolder Else TrailingSlash = strFolder & "\" End If End If
Click inside the "extractExcelFiles" sub-procedure and click "F5" to run the program.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for