How to scan directory , list all the file

[moonhk]

Hi
How to scan directory, list all the file under particluar directory. I
want send those files by mail. Eeach time just send a file each time.

e.g. under c:\application\statement have a lot of *.xls. How to list
out all the file ?

 

[WhytheQ]

This code from Bob Phillips for listing file names and adding them to a
list box is a cool way of doing it, traditionally I thought this was
done using the dir method, but the below looks better:

Sub CreateFormsListBox()
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim sPath As String
Dim oList As ListBox


Application.CommandBars("Forms").Visible = True
ActiveSheet.ListBoxes.Add(18, 12.75, 150, 200).Select
Selection.OnAction = "myPrintMacro"
Set oList = Selection


Set FSO = CreateObject("Scripting.FileSystemObject")


sPath = "C:\MyTest"
Set Folder = FSO.GetFolder(sPath)


Set Files = Folder.Files
For Each file In Files
oList.AddItem file.Name
Next file


Range("A1").Select


End Sub


I'm sure you can easily adapt the above.
Rgds
J

 

[Don Guillett]

Here is one you can adapt using DIR

Sub anotherfindfiles()
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MediaFileLocation As String
MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[moonhk]

Thank Both
Just test below coding , it works.
Option Explicit

Sub anotherfindfiles()
'2006/10/23
'http://groups.google.com/group/microsoft.public.excel.programming/
'
browse_frm/thread/29c831da7ff12191/c93692d9863e97fd#c93692d9863e97fd
Application.ScreenUpdating = False
Dim FN As String ' For File Name
Dim Msg As String

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = "D:\Example\EXCEL\*.xls"
FN = Dir(MediaFileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
'Cells(ThisRow, 1) = FN
Msg = Msg + FN + Chr(13)
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox Msg
End Sub