List file names using VBA Dir instruction

[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?.

 

[Jan Karel Pieterse]

Hi 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?.

The default one (you can omit the 2nd argument).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[OssieMac]

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