Loop Through Files in a Folder with Excel VBA

This code will help you access each file within a folder and perform operations on them.

Problem:

You want to loop through all files in a folder using Excel VBA. This could be useful for tasks like processing data from multiple files, extracting information, or performing batch operations on a set of files.

Solution: Looping Through Files in a Folder

Here’s the full code with a detailed explanation:

Sub LoopThroughFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim folder As Object
    Dim file As Object
    Dim fso As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    ' Prompt the user to select a folder
    folderPath = GetFolderPath()
    If folderPath = "" Then Exit Sub ' If no folder is selected, exit the subroutine
    ' Set up the File System Object (FSO) for handling files
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Check if the folder exists
    If Not fso.FolderExists(folderPath) Then
        MsgBox "Folder does not exist!"
        Exit Sub
    End If
    ' Set the folder object
    Set folder = fso.GetFolder(folderPath)
    ' Create a new worksheet to store results (optional)
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "File Data"
    ' Write headers to the worksheet
    ws.Cells(1, 1).Value = "File Name"
    ws.Cells(1, 2).Value = "File Size (KB)"
    ws.Cells(1, 3).Value = "Last Modified"
    ' Initialize row for writing data
    lastRow = 2
    ' Loop through each file in the folder
    For Each file In folder.Files
        ' Get the file name
        fileName = file.Name       
        ' Get the file size (in KB)
        fileSize = file.Size / 1024 ' Convert size to KB       
        ' Get the last modified date of the file
        lastModified = file.DateLastModified       
        ' Write the file information into the worksheet
        ws.Cells(lastRow, 1).Value = fileName
        ws.Cells(lastRow, 2).Value = fileSize
        ws.Cells(lastRow, 3).Value = lastModified       
        ' Move to the next row
        lastRow = lastRow + 1
    Next file   
    ' Notify user that the operation is complete
    MsgBox "Files processed successfully!"  
End Sub

' Function to prompt the user to select a folder
Function GetFolderPath() As String
    Dim folderPicker As FileDialog
    Set folderPicker = Application.FileDialog(msoFileDialogFolderPicker)   
    folderPicker.Title = "Select Folder"  
    ' Show the dialog box and get the folder path
    If folderPicker.Show = -1 Then
        GetFolderPath = folderPicker.SelectedItems(1)
    Else
        GetFolderPath = ""
    End If
End Function

Explanation of Code:

1. Get Folder Path (GetFolderPath Function):
   • This function opens a folder picker dialog box to allow the user to select a folder. If the user selects a folder, it returns the folder path; otherwise, it returns an empty string.
   • FileDialog(msoFileDialogFolderPicker) creates a dialog that lets the user pick a folder.
   • The Show method opens the dialog, and if the user selects a folder (returns -1), the path is returned.
2. Setting Up the FileSystemObject (FSO):
   • CreateObject(« Scripting.FileSystemObject ») creates an instance of the FileSystemObject. This is a powerful tool for working with files and folders in VBA.
   • We use this object to get information about the files in the folder (such as file size, name, and last modified date).
3. Check if Folder Exists:
   • fso.FolderExists(folderPath) checks if the folder path specified exists. If it doesn’t, a message box is shown, and the subroutine exits.
4. Accessing the Files in the Folder:
   • Set folder = fso.GetFolder(folderPath) retrieves the folder object.
   • For Each file In folder.Files loops through every file in the folder.
   • For each file, the code retrieves:
     • File Name (file.Name): The name of the file.
     • File Size (file.Size / 1024): The size of the file in KB (originally in bytes, so we divide by 1024).
     • Last Modified Date (file.DateLastModified): The date and time the file was last modified.
5. Writing Data to the Worksheet:
   • The code writes the file name, size, and last modified date into an Excel worksheet.
   • The ws.Cells(lastRow, 1).Value writes the data into the corresponding cell of the worksheet. The lastRow variable is incremented after each file to ensure the data is written to the next row.
6. Creating a New Worksheet:
   • A new worksheet is added using Set ws = ThisWorkbook.Worksheets.Add. This worksheet will contain the information about the files.
   • The headers « File Name, » « File Size (KB), » and « Last Modified » are written to the first row of the worksheet.
7. Completion Message:
   • Once the loop completes, the code displays a message box notifying the user that the files were processed successfully.

Practical Uses:

• Batch File Operations: You can modify this script to process files (e.g., open them, extract data, and save them).
• File Audits: Use this code to list files, their sizes, and last modified dates for auditing purposes.
• Data Collection: Collect metadata about files in a directory and use it for analysis or reporting.

Modifying the Code:

• Perform Operations on Files: You can modify the loop to perform operations on each file, such as opening the file, extracting data, or copying it.
• Filter Files: To filter for specific types of files (e.g., only .xls files), you can use an If statement inside the loop, like this:
• If fso.GetExtensionName(file.Name) = « xls » Then
•     ‘ Perform actions on Excel files only
• End If

This should give you a solid foundation to start looping through files in a folder using VBA!