Skip to Content

For each file in folder using VBA(FileSystemObject)

Iterate through each file in a folder using VBA FileSystemObject

Sub LoopThroughFilesFSO() Dim fso As Object Dim folder As Object Dim file As Object Dim folderPath As String ' Step 1: Specify the folder path folderPath = "C:\YourFolderPath" ' Replace with your folder path ' Step 2: Create FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(folderPath) ' Step 3: Loop through each file in the folder For Each file In folder.Files MsgBox "File Name: " & file.Name, vbInformation, "File Found" Next file ' Step 4: Clean up Set folder = Nothing Set fso = Nothing End Sub


Steps to Use:

  1. Open the VBA Editor: Press Alt + F11.
  2. Insert a Module: Go to Insert > Module.
  3. Paste the Code: Copy and paste the code into the module.
  4. Specify the Folder Path: Replace "C:\YourFolderPath" with your desired folder path.
  5. Run the Macro: Press F5 to execute the code.

Explanation:

  • CreateObject("Scripting.FileSystemObject"): Creates an instance of the FileSystemObject.
  • folder.Files: Returns a collection of files in the specified folder.
  • file.Name: Returns the name of each file.


Key Differences Between FileSystemObject and Dir:

FeatureFileSystemObjectDir
Ease of UseRequires enabling references (if early binding).No additional setup required.
PerformanceSlightly slower for large folders.Faster for simple file listing.
Error HandlingBetter error handling (object-oriented).Limited error handling.
FlexibilityCan retrieve additional file properties.Limited to basic file operations.

Use Cases:

  • Use FileSystemObject if you need to access file properties like size, date created, etc.
  • Use Dir for lightweight, quick file iteration.

in VBA
RKsTechAdemy 10 December 2024
Share this post
Our blogs
Archive
Sign in to leave a comment
Ways to find last column using VBA