Getting file names in a directory

[John]

Hello,
Would anyone be able to tell me how to get a list of files
in a particular directory within a VBA routine in Excel
2003?

Thanks!

 

[Guest]

Try:

Sub xz()
Dim Files As Variant
Files = ""
Set fso = CreateObject("Scripting.FileSystemObject")
For Each File In fso.GetFolder("C:\AJAY").Files
Files = Files & "," & File.Name
Next
Files = Split(Mid(Files,2), ",")
Set fso = NOTHING
End Sub

NOTE:
1. Files is a zero-based variant array irrespective of your option base
setting.
2. This does NOT recurse flders below the one you specify, if any.

 

[Don Guillett]

try
sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\myfolder\*.xls"

FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub