Skip to Content

For each file in folder using VBA(Dir)

Iterate through each file in a folder using VBA Dir Function

Sub LoopThroughFilesDir() Dim folderPath As String Dim fileName As String ' Step 1: Specify the folder path folderPath = "C:\YourFolderPath\" ' Ensure the folder path ends with "\" ' Step 2: Get the first file in the folder fileName = Dir(folderPath & "*.*") ' Use wildcard to list all files ' Step 3: Loop through all files Do While fileName <> "" MsgBox "File Name: " & fileName, vbInformation, "File Found" fileName = Dir ' Get the next file Loop End Sub


Steps to Use:

  1. Follow the same steps as above to insert and run the macro.
  2. Update the folderPath variable with your desired folder path.

Explanation:

  • Dir(folderPath & "*.*"): Retrieves the first file in the folder.
  • fileName = Dir: Moves to the next file in the folder during the loop.


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
For each file in folder using VBA(FileSystemObject)