listing excel file in a folder

  • Thread starter Thread starter asrul
  • Start date Start date
A

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
 
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
 
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
 
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)
 
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)
 
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.
 
Back
Top