How to get the Name, Author, Album and Title of all the Music Files in a Certain Folder through VBA?

[Kartick0075]

Dear Respected Ones,
Hi, everyone. I'm a music lover. I have more than 1750 songs in my computer. I came to know this site a few months ago. I found it very helpful for learning. I'm learning VBA File and Folder Handling. For taking my learning to next level, I want to have the Name, Author, Album and Title of each and every file of these files; that are kept in my my "D" Drive ("D:\Music\Files..."); through VBA. I wrote a code. But, it only gave me the File Name of these music files. But, I don't know, how will I get the other attributes of these files like their Authors, Albums and Titles. Can you please help me.... I'm attaching my written code in below..

-----------------------------

Option Explicit

Sub ImportingFileDetails()

        On Error Resume Next
       
        Dim FDB As Office.FileDialog
        Set FDB = Application.FileDialog(msoFileDialogFolderPicker)
       
        With FDB
                .ButtonName = "Select Folder"
                .InitialFileName = "Choose Desired Folder"
                .InitialView = msoFileDialogViewPreview
                .Title = "Get Files' Names"
                .Show
        End With
       
        Dim SelectedFolder As String
        SelectedFolder = FDB.SelectedItems(1)
       
        Dim FSO As Scripting.FileSystemObject
        Set FSO = New Scripting.FileSystemObject
       
        Dim ChoosenFolder As Scripting.Folder
        Set ChoosenFolder = FSO.GetFolder(SelectedFolder)

        If ChoosenFolder Is Nothing Then
                MsgBox "No Folder is selected"
                Exit Sub
        Else
                Dim FilesUnderChoosenFolder As Scripting.File
               
                Dim SerialNumber As Integer
                SerialNumber = 1
               
                Dim i As Integer
                i = 5
               
                Dim FilesCountUnderChoosenFolder As Integer
                FilesCountUnderChoosenFolder = 0
               
                Range("B5", Range("B5").End(xlDown).Resize(1, 5)).ClearContents
                       
                For Each FilesUnderChoosenFolder In ChoosenFolder.Files
                        ImportFileDetails.Cells(i, 1).Value = SerialNumber
                        ImportFileDetails.Cells(i, 2).Value = FilesUnderChoosenFolder.Name
                        ImportFileDetails.Cells(i, 3).Value = FilesUnderChoosenFolder.Attributes("Author")
                        ImportFileDetails.Cells(i, 4).Value = FilesUnderChoosenFolder.Attributes("Album")
                        ImportFileDetails.Cells(i, 5).Value = FilesUnderChoosenFolder.Attributes("Title")
                               
                        SerialNumber = SerialNumber + 1
                        i = i + 1
                        FilesCountUnderChoosenFolder = FilesCountUnderChoosenFolder + 1
                Next FilesUnderChoosenFolder
        End If
       
        Dim FirstBlankRow As Integer
       
        If FilesCountUnderChoosenFolder > 1 Then
                FirstBlankRow = Range("A5").Offset(0, 1).End(xlDown).Offset(1, 0).Row
                Range("A" & FirstBlankRow).Select
                Range(Selection, Selection.End(xlDown)).EntireRow.Delete
        ElseIf FilesCountUnderChoosenFolder = 1 Then
                FirstBlankRow = Range("A5").Offset(1, 1).Row
                Range("A" & FirstBlankRow).Select
                Range(Selection, Selection.End(xlDown)).EntireRow.Delete
        Else
                FirstBlankRow = Range("A5").Offset(1, 1).Row
                Range("A" & FirstBlankRow).Select
                Range(Selection, Selection.End(xlDown)).EntireRow.Delete
                Range("A5").Value = ""
        End If
       
        ImportFileDetails.Range("B4").Value = "File Names"
        ImportFileDetails.Range("B5").Activate
               
End Sub

 

[Domenic]

The following macro uses the Windows Shell object to both prompt the user to select a folder, and list the file properties. It also uses early binding, so you'll need to set a reference to Microsoft Shell Controls and Automation...

Visual Basic Editor >> Tools >> References >> and check/select Microsoft Shell Controls and Automation

Note that the list will be created on the active sheet. Also, you'll need to change the root/starting folder, where specified. Here's the code...

Option Explicit

Sub ImportFileDetails()
  
    Dim oShell As Shell32.Shell
    Set oShell = New Shell32.Shell
      
    Dim oFolder As Shell32.Folder
    Set oFolder = oShell.BrowseForFolder(0, "Select a folder", 0, "C:\Users\Domenic\Music") 'change the root/starting folder accordingly
  
    If oFolder Is Nothing Then
        MsgBox "No folder is selected.", vbExclamation
        Exit Sub
    End If
  
    'insert column headers
    Range("A1").Resize(1, 5).Value = Array("Serial Number", "Name", "Author", "Album", "Title")
  
    'list files and their attributes
    Dim oFolderItem As FolderItem
    Dim itemIndex As Long
    Dim rowIndex As Long
    rowIndex = 2
    With oFolder
        For itemIndex = 0 To .Items.Count - 1
            Set oFolderItem = oFolder.Items.Item(itemIndex)
            Cells(rowIndex, "A").Value = itemIndex + 1
            Cells(rowIndex, "B").Value = oFolderItem.Name
            Cells(rowIndex, "C").Value = oFolder.GetDetailsOf(oFolderItem, 20)
            Cells(rowIndex, "D").Value = oFolder.GetDetailsOf(oFolderItem, 14)
            Cells(rowIndex, "E").Value = oFolder.GetDetailsOf(oFolderItem, 21)
            rowIndex = rowIndex + 1
        Next itemIndex
    End With
  
    Set oFolder = Nothing
    Set oShell = Nothing
  
End Sub

Hope this helps!

 

[Kartick0075]

The following macro uses the Windows Shell object to both prompt the user to select a folder, and list the file properties. It also uses early binding, so you'll need to set a reference to Microsoft Shell Controls and Automation...

Visual Basic Editor >> Tools >> References >> and check/select Microsoft Shell Controls and Automation

Note that the list will be created on the active sheet. Also, you'll need to change the root/starting folder, where specified. Here's the code...

Option Explicit

Sub ImportFileDetails()
  
    Dim oShell As Shell32.Shell
    Set oShell = New Shell32.Shell
      
    Dim oFolder As Shell32.Folder
    Set oFolder = oShell.BrowseForFolder(0, "Select a folder", 0, "C:\Users\Domenic\Music") 'change the root/starting folder accordingly
  
    If oFolder Is Nothing Then
        MsgBox "No folder is selected.", vbExclamation
        Exit Sub
    End If
  
    'insert column headers
    Range("A1").Resize(1, 5).Value = Array("Serial Number", "Name", "Author", "Album", "Title")
  
    'list files and their attributes
    Dim oFolderItem As FolderItem
    Dim itemIndex As Long
    Dim rowIndex As Long
    rowIndex = 2
    With oFolder
        For itemIndex = 0 To .Items.Count - 1
            Set oFolderItem = oFolder.Items.Item(itemIndex)
            Cells(rowIndex, "A").Value = itemIndex + 1
            Cells(rowIndex, "B").Value = oFolderItem.Name
            Cells(rowIndex, "C").Value = oFolder.GetDetailsOf(oFolderItem, 20)
            Cells(rowIndex, "D").Value = oFolder.GetDetailsOf(oFolderItem, 14)
            Cells(rowIndex, "E").Value = oFolder.GetDetailsOf(oFolderItem, 21)
            rowIndex = rowIndex + 1
        Next itemIndex
    End With
  
    Set oFolder = Nothing
    Set oShell = Nothing
  
End Sub

Hope this helps!

Thank you Sir.