Excel VBA DIR Function

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

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

Dir(pathname, [attributes])

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.

Wildcard	Explanation
*	Allows you to match any string of any length (including zero length)
?	Allows you to match on a single character

attributes

Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.

Constant	Value	Description
vbNormal	0	(Default) Specifies files with no attributes.
vbReadOnly	1	Specifies read-only files in addition to files with no attributes.
vbHidden	2	Specifies hidden files in addition to files with no attributes.
VbSystem	4	Specifies system files in addition to files with no attributes. Not available on the Macintosh.
vbVolume	8	Specifies volume label; if any other attributed is specified, vbVolume is ignored. Not available on the Macintosh.
vbDirectory	16	Specifies directories or folders in addition to files with no attributes.
vbAlias	64	Specified file name is an alias. Available only on the Macintosh.

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.

Outbound References

https://msdn.microsoft.com/en-us/library/office/gg278779.aspx