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

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

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.

excel_dir_function

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.

excel_dir_function_02

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

excel_dir_function_03

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *