List file names using VBA Dir instruction

  • Thread starter Thread starter LVS
  • Start date Start date
L

LVS

I want to use VBA Excel 2007 to list the names of files in a particular
directory. I cannot use FileSearch, and need some guidance on use of
Dir("PathName",vbattribute). Which attribute responds to the name of the
file?.
 
See if the following example help. One retrieves Directories and the other
retrieves files.

Sub Test_Dir_Path()
'Returns directories for specified path

Dim MyPath As String
Dim myDirectory As String

MyPath = CurDir & "\" ' Set the path and add "\"

MsgBox "Current path is:- " & Chr(13) & _
MyPath 'For testing purposes

' Retrieve the first directory entry.
myDirectory = Dir(MyPath, vbDirectory)
Do While myDirectory <> "" ' Start the loop.
' Ignore the current and encompassing directory.
If myDirectory <> "." And myDirectory <> ".." Then
' Test that is a directory
If (GetAttr(MyPath & myDirectory) And vbDirectory) _
= vbDirectory Then
MsgBox myDirectory 'For testing purposes
End If
End If
myDirectory = Dir 'Get next entry.
Loop
End Sub


Sub Test_Dir_Files()
'Returns files for specified path

Dim MyPath As String
Dim myFile As String

MyPath = CurDir & "\" ' Set the path and add "\"

MsgBox "Current path is:- " & Chr(13) & _
MyPath 'For testing purposes

' Retrieve the first entry.
myFile = Dir(MyPath)
Do While myFile <> "" ' Start the loop.
MsgBox myFile
myFile = Dir 'Get next entry.

Loop
End Sub
 
Back
Top