This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder.
You may also want to read:
Excel loop workbooks in folders and subfolders with FileSystemObject
Excel VBA DIR Function
DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes).
DIR Function is usually used with loop in order to manipulate multiple files of specific name.
Syntax of DIR Function
|pathname||Optional. String expression that specifies a folder path+file name or folder path . A zero-length string (“”) is returned if pathname is not found. Support wildcard characters.
|attributes||Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.
Normally DIR Function only retrieves the first matched file name, but you can use Dir with no argument to retrieve additional file name (refer to the example 2).
Example of DIR Function
In this example, I create a folder called “testing” with some workbooks inside and place it in Desktop.
Example 1: Test Folder Path
Create a Sub Procedure as below
Public Sub firstFileName() Filename = Dir("C:\Users\WYMAN\Desktop\testing\") MsgBox (Filename) End Sub
The result is 11.xlsx, which is the first file in the testing folder. The first file means the first file in ascending file name.
Example 2: Loop through workbook of specific file name
The below code finds all files with xlsx extension and file name starting with 2.
The purpose of code Filename = Dir() in the end is to find additional file name.
Public Sub LoopFileName() Filename = Dir("C:\Users\WYMAN\Desktop\testing\2*.xlsx") Do While Filename <> "" MsgBox (Filename) Filename = Dir() Loop End Sub
Example 3: Test if a file exists
Public Sub LoopFileName() Filename = Dir("C:\Users\WYMAN\Desktop\testing\2*.xlsx") If Filename = "" Then MsgBox "file not found" Else Do While Filename <> "" MsgBox (Filename) Filename = Dir() Loop End If End Sub
Alternate solution to loop through workbooks in a folder
You can also loop through workbooks using File System Object (FSO), which I think is a better solution than DIR Function because it supports additional Methods such as deleting file objects.