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

"asrul" wrote:

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

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
"Mike H" wrote:

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

"asrul" wrote:

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




"asrul" wrote in message
...
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

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 Sub


--
mudraker

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

www.thecodecage.com
------------------------------------------------------------------------
mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=69125

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)


On Fri, 27 Feb 2009 17:13:06 +0700, "asrul" wrote:

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

"Mike H" wrote in message
...

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
"Mike H" wrote:

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

"asrul" wrote:

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