Reading files on a specific folders

J

Jaime

How would I read the content of a folder and output all
files names (such as file names, file type, date & time,
etc) into a spreadsheet.

Any Idea?

Thanks in advance!
Jaime
 
R

Ron de Bruin

Try this

Run this with a empty sheet active

Sub test2()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
R

Rog

Jaime, the following code requires you to set a reference
to Microsoft Scripting Runtime - do this by going to the
menu items Tools|references

change your starting PATH to the root folder, and run the
getfiles macro



Public Sub GetFiles()

Const PATH As String = "C:\Mcskew"
Dim objFSo As New FileSystemObject
Dim objFolder As Folder
Dim rngOut As Range

Set rngOut = Range("A1")
Set objFolder = objFSo.GetFolder(PATH)

Process rngOut, objFolder

End Sub

Public Sub Process(ByRef p_rngOut As Range, ByRef
p_objFolder As Folder)

Dim objFolder As Folder
Dim objFile As File

For Each objFile In p_objFolder.Files
p_rngOut.Value = objFile.Name
p_rngOut.Offset(0, 1).Value = objFile.PATH
p_rngOut.Offset(0, 2).Value = objFile.Type
Set p_rngOut = p_rngOut.Offset(1)
Next

For Each objFolder In p_objFolder.SubFolders
Process p_rngOut, objFolder
Next

End Sub



Rgds

Rog

PS isn't recursion great!
 
Top