Dim FileList As String: FileList = CreateObject("Wscript.Shell").Exec("cmd /c dir ""c:\*"" /S /B").StdOut.ReadAll
'---------
Public Sub ScanSubfolders()
'---------
Dim FileSystem As Object
Dim HostFolder As String
Range("A1").Value = "Folder"
Range("b1").Value = "File"
Range("c1").Value = "Modify Date"
Range("A2").Select
HostFolder = "C:\myFoldername\"
Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub
'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
Dim oFile
For Each SubFolder In Folder.SubFolders
'Debug.Print SubFolder
DoFolder SubFolder
Next
For Each oFile In Folder.Files
' Operate on each file
' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
If InStr(oFile, ".xls") > 0 Then
ActiveCell.Value = Folder
ActiveCell.Offset(0, 1).Value = oFile.Name
ActiveCell.Offset(0, 2).Value = oFile.DateLastModified
ActiveCell.Offset(1, 0).Select 'next row
End If
Next
End Sub
run: ScanSubfolders, after you set the foldername: HostFolder = "C:\myFoldername"
Code:'--------- Public Sub ScanSubfolders() '--------- Dim FileSystem As Object Dim HostFolder As String Range("A1").Value = "Folder" Range("b1").Value = "File" Range("c1").Value = "Modify Date" Range("A2").Select HostFolder = "C:\myFoldername\" Set FileSystem = CreateObject("Scripting.FileSystemObject") DoFolder FileSystem.GetFolder(HostFolder) End Sub '--------- Private Sub DoFolder(Folder) '--------- Dim SubFolder Dim oFile For Each SubFolder In Folder.SubFolders 'Debug.Print SubFolder DoFolder SubFolder Next For Each oFile In Folder.Files ' Operate on each file ' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5) If InStr(oFile, ".xls") > 0 Then ActiveCell.Value = Folder ActiveCell.Offset(0, 1).Value = oFile.Name ActiveCell.Offset(0, 2).Value = oFile.DateLastModified ActiveCell.Offset(1, 0).Select 'next row End If Next End Sub
'---------
Public Sub ScanSubfolders()
'---------
Dim FileSystem As Object
Dim HostFolder As String
Range("A1").Value = "Drive"
Range("b1").Value = "Subfolder 1"
Range("c1").value = "Subfolder 2"
Range("d1").value = "Subfolder 3"
Range("e1").Value = "File"
Range("f1").Value = "Modify Date"
Range("A2").Select
HostFolder = "M:"
Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub
'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
Dim oFile
For Each SubFolder In Folder.SubFolders
'Debug.Print SubFolder
DoFolder SubFolder
Next
For Each oFile In Folder.Files
' Operate on each file
' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
If InStr(oFile, ".xls") > 0 Then
ActiveCell.Value = Folder
ActiveCell.Offset(0, 1).Value = oFile.Name
ActiveCell.Offset(0, 2).Value = oFile.DateLastModified
ActiveCell.Offset(1, 0).Select 'next row
End If
Next
End Sub
add:
Code:'--------- Private Sub DoFolder(Folder) '--------- Dim SubFolder Dim oFile on error resume next
'---------
Private Sub DoFolder(Folder)
'---------
Dim SubFolder
Dim oFile
On Error Resume Next
For Each SubFolder In Folder.SubFolders
'Debug.Print SubFolder
DoFolder SubFolder
Next
For Each oFile In Folder.Files
' Operate on each file
' Debug.Print Folder, oFile.Name, getFileProperty(oFile, 5)
If InStr(oFile, ".xls") > 0 Then
ActiveCell.Value = ??????? 'DISPLAY DRIVE LETTER ONLY
ActiveCell.Offset(0, 1).Value = ???????? 'DISPLAY NAME OF SUBFOLDER 1
ActiveCell.Offset(0, 2).Value = ???????? 'DISPLAY NAME OF SUBFOLDER 2
ActiveCell.Offset(0, 3).Value = ???????? 'DISPLAY NAME OF SUBFOLDER 3
ActiveCell.Offset(0, 4).Value = oFile.Name
ActiveCell.Offset(0, 5).Value = oFile.DateLastModified
ActiveCell.Offset(1, 0).Select 'next row
End If
Next
End Sub