Listing files in a MS Explorer folder in spreadsheet

[drdrfaulkner]

I have a folder in Windows Explorer containing numerous files I want to list in a spreadsheet without cutting an pasting them individually. Is there a way I can do this?

 

[Mark O'Brien]

Here's an example ripped directly from XL-Dennis in the thread:

http://216.92.17.166/board/viewtopic.php?topic=21442&forum=2

I liked Ivan's solution, but couldn't quite get it to work. I especially liked the way he got the directoy path as well, but I couldn't be bothered ripping that out and incorporating it. I've tinkered with XL-Dennis's solution so that you don't have to tinker with any references.<pre>
Sub List_Files()
Dim fsoObj As Object 'Scripting.FileSystemObject
Dim fsoMapp As Object 'Scripting.Folder
Dim fsoFil As Object 'Scripting.File
Dim sFolder As String
Dim i As Long

'Change this to whatever directory you want
sFolder = "C:Temp"

Set fsoObj = CreateObject("Scripting.FileSystemObject")

'Set fsoObj = New Scripting.FileSystemObject
Set fsoMapp = fsoObj.GetFolder(sFolder)

With Range("A1:H1")
.Value = Array("Filename", "Created", "Last changed", "Size", "Type", _
"Drive", "Folder", "Path")
.Font.Bold = True
End With

i = 0
If Not fsoMapp Is Nothing Then
For Each fsoFil In fsoMapp.Files
If fsoFil Like "*.xls" Then
i = i + 1
With fsoFil
Cells(1 + i, 1).Value = .Name
Cells(1 + i, 2).Value = .DateCreated
Cells(1 + i, 3).Value = .DateLastModified
Cells(1 + i, 4).Value = .Size
Cells(1 + i, 5).Value = .Type
Cells(1 + i, 6).Value = .Drive
Cells(1 + i, 7).Value = .ParentFolder
Cells(1 + i, 8).Value = .Path
End With
End If
Next
End If

Columns("A:H").EntireColumn.AutoFit

Set fsoFil = Nothing
Set fsoMapp = Nothing
Set fsoObj = Nothing
End Sub</pre>

HTH

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-10-17 09:51

 

[drdrfaulkner]

I tried the script, but got only column names across the top of the spreadsheet. I'll try again tomorrow.