How to Extract Excel Files From Multiple Folders With VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Extract Excel Files From Multiple Folders With VBA
Search for files in different directories using VBA. (Chad Baker/Digital Vision/Getty Images)

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:
Moderate

Other People Are Reading

Things you need

  • Microsoft Office

Show MoreHide

Instructions

  1. 1

    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.

  2. 2

    Type the following to create a new sub-procedure:

    Dim col Files As New Collection

  3. 3

    Copy and paste the following to create your variables:

    Dim extract Path As String
    
    Dim filename As String
    
    Dim pos As Integer
    
  4. 4

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

    extract Path = "C:\Temp\"
    
  5. 5

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

    RecursiveDir col Files, "F:\moreExcelFiles\", "*.xlsx", True
    
  6. 6

    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
    

    Next vFile

    End sub

  7. 7

    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
    

    End Function

  8. 8

    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
    

    End Function

  9. 9

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.