listing excel file in a folder

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
 
M

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
 
M

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
 
W

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
[email protected]
 
M

mudraker

Change path to suit

Add other Excel file types as required by changing
Case "xls"
to
Case "xls", "xlm"





Sub ListExcelFiles()
Dim lRow As Long
Dim sPath As String
Dim sFname As String
Dim sTmpArr() As String

sPath = "c:\Temp\"

sFname = Dir(sPath, vbNormal)
lRow = 1
Do Until sFname = ""
sTmpArr = Split(sFname, ".")
Select Case LCase(sTmpArr(UBound(sTmpArr)))
Case "xls"
lRow = lRow + 1
Cells(lRow, "a").Value = sFname
End Select
sFname = Dir
Loop
End Su

--
mudrake

If my reply has assisted or failed to assist you I welcome you
Feedback.

www.thecodecage.co
 
C

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)
 
A

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