Excel Dir Function (VBA)
-function/dir-function-vba-code.jpg)
How to use the Dir Function (VBA)
This Excel tutorial explains how to use the Excel Dir function with syntax and examples using VBA.
SUMMARY:
The Excel VBA Dir function returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
The VBA Dir function returns the first filename that matches the path and, if needed, the attributes specified. To retrieve the subsequent paths/filenames that match the provided path and attributes you need to call Dir again with no arguments.
PURPOSE:
To returns the name of a file or directory matching a pattern or attribute (String).
RETURN VALUE:
The Dir function returns a string value.
SYNTAX:
Dir[(pathname,[attributes])]
ARGUMENTS:
The Dir function syntax has the following arguments:
- pathname: Optional. String expression that specifies a file name; may include directory or folder, and drive.
- A zero-length string ("") is returned if pathname is not found.
- attributes: Optional. Constant or numeric expression, whose sum specifies file attributes.
- If omitted, returns files that match pathname but have no attributes.
- The attributes argument settings are:
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 attribute 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.
REMARKS:
- The "pathname" may include a directory and drive.
- If "pathname" cannot be found, then a zero length string ("") is returned.
- The "attribute" can be a constant or a numerical expression.
- If "attributes" is left blank, then 0 is used. These are files that match "pathname" but have no attributes.
- In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files.
Wild Card Character Description * Allows you to match any string of any length (including zero length). ? Allows you to match on a single character. - On the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.
- To iterate over all the files in a folder, specify an empty string for the "pathname".
- To get additional file names, call this function again with no arguments.
- When no more files exist an empty string is returned.
-function/dir-function-vba-code-remark1.jpg)
Dir Function Example (as VBA code)
The Dir function can only be used in VBA code in Microsoft Excel.The following VBA code shows how the Dir function used to get the file path.
Dim testResult As String
testResult = Dir("C:\myprofile.jpg")
In the above VBA code example, The testResult variable would now contain the filename of the myprofile.jpg file.
VBA Dir Function Error:
- You must provide a "pathname" the first time that this function is called, otherwise an error will occur.
- Any subsequent calls must specify a "pathname" otherwise an error will occur.