The following is a repeat of what I posted at
http://www.mrexcel.com/board2/viewtopic.php?t=85085&postdays=0&postorder=asc&start=10 with some amendments.
All you have to do is to change the stuff between the double lines, specifically:
Your path – include the quote marks around the path and the final back slash.
Filetype – if you want all files put “*”; if you want just Excel files, put “xls”; zipped files – “zip” etc.
Worksheet reference i.e. where the data will go. Currently set as Sheet2 (if this is OK, then leave as is).
Startrow this is the row from which your data will start. Currently set as row 2 (if this is OK, then leave as is).
The macro is set to show all files. Just change the path of where your files are located.
Code:
Sub ListFiles2()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim i As Integer
Dim startrow As Integer
Dim ws As Worksheet
Dim filetype As String
'=======================================================
fPath = "C:\Temp\"
filetype = "*"
Set ws = Worksheets("Sheet2")
startrow = 2 'starting row for the data
'========================================================
fName = Dir(fPath & "*." & filetype)
While fName <> ""
i = i + 1
ReDim Preserve fileList(1 To i)
fileList(i) = fName
fName = Dir()
Wend
If i = 0 Then
MsgBox "No files found"
Exit Sub
End If
For i = 1 To UBound(fileList)
ws.Range("A" & i + startrow).Value = fileList(i)
Next
Columns(1).AutoFit
End Sub
See this line in the macro:
fName = Dir(fPath & "*." & filetype)
When the above line incorporates the first two defined variables (in-between the double lines above), it becomes:
fName = Dir(“C:\Temp\*.*”)
i.e. pick up all files.
The next post is the non-macro method.
HTH
Mike