listing excel file in a folder

[asrul]

Hi All,

I want to listing all excel file in a directory.
And put in a worksheet.
How to code it.

Thank's in advance

 

[Mike H]

Hi,

If all you want is the filename then right click your sheet tab, view code
and paste this in and run it. Change MyPath to your desired path

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
x = 1
MyPath = "C:\" 'Change this to your directory
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Cells(x, 1).Value = ActiveFile
x = x + 1
ActiveFile = Dir()
Application.DisplayAlerts = True
End Sub

Mike

 

[Mike H]

Something went wrong in pasting this, try this instead

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
x = 1
MyPath = "C:\" 'Change this to your directory
ActiveFile = Dir(MyPath & "*.xls")
Do While ActiveFile <> ""
Cells(x, 1).Value = ActiveFile
x = x + 1
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Mike

 

[William]

Hi asrul

Sub ListFiles()
Dim i As Integer
With Application.FileSearch
..NewSearch
..LookIn = "C:\MyFolder\MySubFolder" 'change as required
..SearchSubFolders = True
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.Range("A65000").End(xlUp).Offset(1, 0) = .FoundFiles(i)
Next i
End With
End Sub


--

Regards

William
(e-mail address removed)

 

[Chip Pearson]

You can use the following code:

Sub AAA()
Dim R As Range
Dim N As Long
Dim FName As String
Dim FolderName As String
Set R = Range("A1") '<<< Listing start cell
FolderName = "C:\SiteStats" '<<< Change to directory to list
FName = Dir(FolderName & "\*.xl*")
Do Until FName = vbNullString
R.Value = FName ' <<< File name only
' OR
R.Value = FolderName & "\" & FName
Set R = R(2, 1)
FName = Dir()
Loop
End Sub


Change the lines marked with <<< to the appropriate values. You may
also be interested in my Directory Tree Builder add-in that will do
this and much more. See http://www.cpearson.com/excel/FolderTree.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[asrul]

Hi Mike,
Thank's,but I don't get the file name list in my worksheet.
Is there something I miss.
Another request,I also want to add the creation date of the
file in my list.
Thank you.