Thursday 2 May 2013

Excel VBA: The Dir Function


In Microsoft Excel VBA, the Dir function is used to return the first filename from a specified directory, and list of attributes. The filename is returned as a string.
The Dir function can then be entered without any arguments to return the next filename from that directory.
The most common use of the Dir function is to loop through all the files in a folder to perform an action on each one. Other common uses include checking if a file or a directory exists, or to look for a specific file such as the latest one.

Dir Function Syntax

When entering the Dir function into the Visual Basic Editor the quick info list should appear and prompt for the following.
Dir( [PathName], [Attributes As VbFileAttribute = vbNormal]) As String


Pathname: The directory or folder that you want to use. This can be entered as a string, or a variable that contains one. It should include the backslash (\) at the end.
The filename can be entered if you are looking for a specific file. Wildcard characters can also be used to identify types of files, such as (*.xlsx) for a workbook.
Attributes: A list of attributes is shown in the table below. This is optional and a combination can be used. Attributes can be included for a more targeted match.
For example the vbDirectory attribute can be used to specify that you want to return a directory and not a filename.
Attribute
Value
Description
vbNormal
0
Normal (Default)
vbReadOnly
1
Read only
vbHidden
2
Hidden
vbSystem
4
System file
vbVolume
8
Volume label
vbDirectory
16
Directory or folder
vbAlias
64
Filename is an alias


Examples

Example 1 – Loop Through the Files in a Folder


Sub AllFiles()

Dim MyFolder As String ‘Path containing the files for looping
Dim MyFile As String ‘Filename obtained by Dir function
 
MyFolder = “C:\ExcelFiles” ‘Assign directory to MyFolder variable

MyFile = Dir(MyFolder) ‘Dir gets the first file of the folder

‘Loop through all files until Dir cannot find anymore
Do While MyFile <> ""

    The statements you want to run on each file
   
    MyFile = Dir ‘Dir gets the next file in the folder

Loop

End Sub

Example 2 – List the Files from a Folder on a Worksheet


Sub ListFiles()

Dim MyDirectory As String ‘Folder containing the files
Dim MyFile As String ‘The filename to enter on the worksheet
Dim NextRow As Long ‘The row for the next filename in list

MyDirectory = “C:\ExcelFiles” ‘Assign directory to MyDirectory variable

MyFile = Dir(MyDirectory) ‘Dir gets the first file in the folder

‘Find the next empty row in the list and store in NextRow variable
NextRow = Application.CountA(Range(“A:A”)) + 1

Do Until MyFile = “”

      Cells(NextRow, 1).value = MyFile
     
      NextRow = NextRow + 1 ‘Move to the next row

      MyFile = Dir ‘Dir gets the name of next file in the folder
Loop

End Sub

Example 3 – Check if a Files Exists


Sub FileExists()

      Dim TheFolder As string ‘Location of the file
      Dim FiletoCheck As String ‘Name of the file you want to check

TheFolder = “C:\ExcelFiles” ‘Assign directory to TheFolder variable

‘Capture the name of file to check for using an input box
FiletoCheck = InputBox(“Enter the name of the file you want to look for”, “Enter file name”)

‘If FiletoCheck is an empty string then file not found
If FiletoCheck = “” Then
     
      Msgbox “Oh no, the file does not exist”

Else

      Msgbox “Yes, the file exists.”

End If

End Sub

-->

5 comments:

  1. You forgot to use Dir in Example 3

    ReplyDelete
    Replies
    1. This comment has been removed by a blog administrator.

      Delete
  2. Sub AllFiles()

    Dim MyFolder As String 'Path containing the files for looping
    Dim MyFile As String 'Filename obtained by Dir function
    Dim filepath As Worksheet
    Dim xrow As Long

    Set filepath = Worksheets("filepath")
    filepath.Select
    Range("a1").Select
    MyFolder = ThisWorkbook.Path & "\SkuImage\" 'Assign directory to MyFolder variable

    MyFile = Dir(MyFolder) 'Dir gets the first file of the folder
    Range("a1").Value = MyFolder & MyFile
    'Loop through all files until Dir cannot find anymore
    xrow = 1
    Do While MyFile <> ""

    'The statements you want to run on each file

    MyFile = Dir 'Dir gets the next file in the folder
    ActiveCell.Offset(xrow, 0).Value = MyFolder & MyFile
    xrow = xrow + 1
    Loop





    End Sub

    ReplyDelete
  3. Sub PicturePathCreation()

    Dim xpath As String
    Dim Xfile, xfolder, xdir


    xdir = "C:\"

    With Application.FileDialog(msoFileDialogFolderPicker)

    .InitialFileName = Application.DefaultFilePath

    .Show
    .Title = "Please Select the folder"
    .InitialFileName = xfolder

    If .SelectedItems.Count <> 0 Then
    Xfile = .SelectedItems(1) & "\"
    xdir = Dir(Xfile, 7)
    Do While xdir <> ""
    ActiveCell.Offset(xrow) = Xfile & xdir
    xrow = xrow + 1
    xdir = Dir

    Loop

    End If


    End With

    ReplyDelete
  4. thanks but your code is very useful and simpler for own.

    ReplyDelete

.