Checking the Existence of a File or Folder the Dir Function

The Dir function, which returns the name of a file or folder, has the following syntax:

Dir[(pathname[, attributes])]

Both arguments of the Dir function are optional. pathname is a name of a file or folder. You can use one of the following constants or values for the attributes argument: Table 8-1: File attributes



Attribute Name












Volume label



Directory or Folder

The Dir function is often used to check whether a file or folder exists on a disk. If a file or folder does not exist, the null string ("") is returned.

Let's try out the Dir function in several exercises in the Immediate window:

1. In the Immediate window, enter the following: ?Dir("C:\", vbNormal)

As soon as you press Enter, Visual Basic returns the name of the first file in the specified folder. A normal file (vbNormal) is any file, except for one with a Hidden, Volume Label, Directory, Folder, or System file attribute.

To return the names of other files in the current directory, use the Dir function without an argument:

?Dir (andpress Enter)

2. Enter the following instructions in the Immediate window and examine their results as you press Enter:

?mfile mfile = Dir

?mfile mfile = Dir


3. Enter the following instruction in the Immediate window:

If Dir("C:\stamp.bat") = "" Then Debug.Print "File was not found."

Because the stamp.bat file doesn't exist on drive C, Visual Basic writes in the Immediate window the specified text message: "File was not found."

4. To find out whether a file exists on a disk, enter the following statement on one line in the Immediate window:

If Dir ("C:\Autoexec.bat") <>"" Then Debug.Print "This file exists on your C drive."

The Dir function allows you to use the wild cards in the specified pathname—asterisk (*) for multiple characters and question mark (?) for a single character. For example, to find all the configuration settings files in the WINDOWS folder, you can look for all the INI files, as shown below:





WINFILE.INI ?dir control.ini ?dir

EQUIP32.INI ?dir sxpwin32.ini

The procedure shown below writes the names of files in the specified directory to the Immediate window. The LCase$ function causes the names of files to appear in lowercase.

1. Open a new workbook and save it in the Chap08.xls file.

2. Switch to the Visual Basic Editor window and rename the VBA project FileMan.

3. Insert a new module into the FileMan project and rename it DirFunction.

4. Enter the following VBA procedure in the Code window:

Sub MyFiles() Dim mfile As String Dim mpath As String mpath = InputBox("Enter pathname,e.g., C:\Excel") If Right(mpath, 1) <> "\" Then mpath = mpath & "\"

If mfile <> "" Then Debug.Print "Files in the " & mpath _

& "folder" Debug.Print LCase$(mfile) If mfile = "" Then

MsgBox "No files found." Exit Sub End If

Debug.Print LCase$(mfile)

Loop End Sub

The MyFiles procedure shown above asks the user for the pathname. If the path does not end with the backslash, the Right function appends the backslash to the end of the pathname string. Next, Visual Basic searches for all the files (*) in the specified path. If there are no files, a message is displayed. If files exist, the filenames are written to the Immediate window.

5. Enter another procedure in the same module:

Sub GetFiles() Dim nfile As String

Dim nextRow As Integer 'next row index nextRow = 1

With Worksheets("Sheet1").Range("A1") nfile = Dir("C:\", vbNormal) .Value = nfile

.Offset(nextRow, 0).Value = nfile nextRow = nextRow + 1


End With End Sub

The GetFiles procedure gets the names of files located in the root directory of drive C and writes each filename into a worksheet.

0 0

Post a comment