How to Loop Through Files in a Folder Using VBA

1. Loop Through Files in a Folder Using VBA
2. Use the Dir Function
3. Use the File System Object
4. The Better Solution
5. Conclusion

Visual Basic for Applications is an event-driven programming language designed for Microsoft Office. It allows you to automate tasks and customize functionalities according to your requirements.

VBA is quite powerful and allows the user to do everything with just a few code statements.

This article discusses how to loop through files stored in a folder using VBA. Two methods have been discussed below in detail, and code snippets have been provided.

Loop Through Files in a Folder Using VBA

VBA allows users to loop through files stored in a specific directory, and get their names, timestamps, extensions, etc. You can perform any action by accessing the directory, for instance, creating new files or altering the old ones.

Two solutions have been mentioned in this article, each having a different access time. VBA allows users to loop through files in a folder using the Dir function and creating a File System Object.

The different solutions are discussed below.

Use the Dir Function

The following code snippet allows the user to loop through files in a folder using the Dir function. The Dir function is known as the directory function.

It is an in-built VBA function that takes the file’s path as a parameter and returns the file’s name.

If the path represents a folder, the Dir function returns the first file’s name stored on the path. The following code snippet represents how the Dir function helps loop through files in a folder.

Sub example()
    Dim fileName As Variant
    fileName = Dir("C:\User\testfolder\")

    Do While Len(fileName) > 0

        ' Insert any action that must be performed on each file.

        Debug.Print fileName
        ' Prints file name to the immediate window

        fileName = Dir
        ' Set the file name to the next file name

    Loop

End Sub

Note: This is a code snippet and not the entire code. You may edit it according to your requirements.

The Dir function provides a quick and efficient solution. In addition, it also has a fast access time.

If you only want to loop through specific files in the folder, you can apply a test condition with the Dir function. The syntax for using a test condition with the Dir function is shown below.

Dir("C:\User\testfolder\*test*")

The *test* can be any condition you want to apply to the files. For example:

1. If you only want to loop through text files in the folder, use the following command: Dir("C:\User\testfolder\*.txt").
2. If you only want to loop through files that contain "Finance" in their names, use the following command: Dir("C:\User\testfolder\*Finance*").

Note: Arguments of the Dir function can also be changed to return different types of files, such as hidden, system, etc.

Use the File System Object

The second solution creates a File System Object (FSO) to loop through files stored in the folder. Different functionalities of the FSO are used to loop through the files stored in the folder.

A code snippet is shared below.

Sub example()
   Dim Obj As Object, Source As Object, file As Variant
   Set Obj = CreateObject("Scripting.FileSystemObject")
   Set Source = Obj.GetFolder("C:\User\testfolder\")

   For Each file In Source.Files

      If InStr(file.Name, ".txt") > 0 Then
	      Debug.Print file.Name
      End If

   Next file
End Sub

The code snippet sets Source with the specific folder’s path. A For Each loop is then used to loop through all the files in the folder.

If you do not want to loop through all files in the folder, you can use the InStr(file.name, "test") > 0 command and specify the text to filter out the files. If the condition returns true, the actions that need to be performed on those specific files will be mentioned within the if statement.

However, there is one issue with the code snippet mentioned above.

The code’s function file.name has a slow execution time. It requires a few seconds to return the answer to the user.

If you are not using a test to filter out the specific files, you would not require the file.name function. In that case, it would work ideally.

You can easily and quickly loop through all files in the folder through the mentioned code snippet.

FSO Early Binding vs. Late Binding

A File System Object can be declared in two ways - early binding and late binding.

1. Early Binding

   Early binding is when the reference links - the libraries - are explicitly checked or added in VBA. The libraries can be added from Tools > References in VBA.

   The execution will fail if the module is not available on your computer. Early binding is super useful for the development and debugging process.

   It offers Intellisense-editor suggestions for object methods, properties, and named constants. In addition, it is faster than late binding.

   In early binding, check the modules/libraries you require and declare objects in the following manner.

   Dim FSO As FileSystemObject
   Set FSO = New FileSystemObject
   

   Another method to declare objects in early binding is as follows:

   Dim FSO As New FileSystemObject
   

2. Late Binding

   External libraries are not linked in late binding. It is independent of reference links making it ideal for intermachine portability and compatibility.

   It does not offer Intellisense-editor, so the object-specific constants must be explicitly declared and provided by their value.

   The following syntax can be used to declare an object in late binding.

   Dim FSO As Object
   Set FSO = CreateObject("Scripting.FileSystemObject")
   

The Better Solution

The solution with the Dir function works better than the solution that creates objects to get files from the folder. The testing ability with the Dir function works more efficiently than the solution that creates objects to access files.

You can create macros that store and run your required functionality. You can run the same functionality over different folders with the help of macros in Microsoft Office.

Conclusion

Microsoft Office applications offer numerous features for their users. With the addition of programming in the VBA language, it has been easier for users to do automated tasks with only a few lines of code.

There are several solutions to loop through the files in a specific folder, so keep exploring.