file indetail view to excel

[dukeofscouts]

I would like to use Excel to analize my MP3 files. Basically I'd love to take the detail view from the folders I have the files in and transfer that data into excel. Is there a tool I can use to accomplish this?

 

[TheNoocH]

How about something like this?

example of how to call the program...

Sub test()
  ListFilesInFolder "H:\", False
End Sub

Program that does all the work...

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    ' lists information about the files in SourceFolder
    'ListFilesInFolder "g:\data\", True
    'NOTE: need Microsoft Scripting Runtime (scrrun.dll) from Tools, Reference
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    Cells(1, 1).Formula = "Path"
    Cells(1, 2).Formula = "File Name"
    Cells(1, 3).Formula = "File Size"
    Cells(1, 4).Formula = "File Type"
    Cells(1, 5).Formula = "Date Created"
    Cells(1, 6).Formula = "Date Last Accessed"
    Cells(1, 7).Formula = "Date Last Modified"
    Cells(1, 8).Formula = "Attributes"
    r = Cells(Rows.Count, "A").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
    ' display file properties
    Cells(r, 1).Formula = FileItem.Path
    Cells(r, 2).Formula = FileItem.Name
    Cells(r, 3).Formula = FileItem.Size
    Cells(r, 4).Formula = FileItem.Type
    Cells(r, 5).Formula = FileItem.DateCreated
    Cells(r, 6).Formula = FileItem.DateLastAccessed
    Cells(r, 7).Formula = FileItem.DateLastModified
    Cells(r, 8).Formula = FileItem.Attributes
    ' next row number
    r = r + 1 
    Next FileItem
    If IncludeSubfolders Then
    For Each SubFolder In SourceFolder.SubFolders
    ListFilesInFolder SubFolder.Path, True
    Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

 

[dukeofscouts]

Sounds like a plan, I'll give it a try.

 

[TheNoocH]

don't forget this part...this is inside the visual basic editor (NOTE: can use ALT-F11 to open it)

'NOTE: need Microsoft Scripting Runtime (scrrun.dll) from Tools, Reference

 

[dukeofscouts]

Got it to work great. However, even after google-ing for the answer to this follow up, and doing some trial and error methods I've come up against a slight problem. I'd like to be able to also sort the information based on length, or as Windows labels it Duration. I tried changing one of the cells to "fileitem.duration" and "self.fileitem.duration" but niether of these were recognized by excel. Does any one know the code that can crack this?