List all song features

[zelarra821]

Hi guys.

I need your help because I don't see the error.

I'm attaching the Excel spreadsheet I'm working on.

In the "Folder.GetDetailsOf" sheet, I have a table with all the existing and future characteristics of a file, regardless of the file type.

Since I want to extract certain characteristics and I'm tired of trying them one by one, I came up with a solution:

In the "List" sheet, I want it to list all the characteristics in columns for a specific folder and all its subfolders.

I've added the titles so you know the structure I want. That is, starting from row 1 with the titles and going down, all the files should appear. I haven't specified that they should only be MP3s and that it shouldn't include hidden folders, but if you can include them, even better.

My idea is for the titles to be added automatically, not by default. That is, in row 1, take the name of the feature, and then scroll down for each file.

However, when I run the code I used, Excel gets stuck, and I don't see where the error could be coming from.

Option Explicit

Sub ListarArchivos()

    Hoja3.Activate
    Hoja3.Cells.Select
    Selection.ClearContents

    ListFiles "D:\Diego\Mi Música"
  
End Sub

Sub ListFiles(ByVal path1 As String)

Dim fso As Object
Dim subfolder As Object
Dim file As Object
Dim folder As Object
Dim objShell  As Object
Dim objFolder As Object
Dim objFolderItem As Object
Dim Celda As Range
Dim i As Integer

    Set fso = CreateObject("Scripting.FileSystemObject")
  
    Set folder = fso.GetFolder(path1)
  
    Set objShell = CreateObject("Shell.Application")
  
    i = 1
      
    For Each subfolder In folder.SubFolders
  
        ListFiles (subfolder.Path)
  
    Next subfolder
  
    For Each file In folder.Files
      
        Set objFolder = objShell.Namespace(folder.Path)
      
        If (Not objFolder Is Nothing) Then
      
            Set objFolderItem = objFolder.ParseName(file.Name)
      
            If (Not objFolderItem Is Nothing) Then
          
                For Each Celda In Range("Folder.GetDetailsOf[iColum]")
          
                    Hoja3.Range("A" & Columns.Count).End(xlToLeft).Offset(1, i) = objFolder.GetDetailsOf(objFolderItem, Celda.Value)
                  
                    i = i + 1
              
                Next Celda
                                  
            End If
          
        End If
  
    Next file
  
    Set Celda = Nothing
    Set file = Nothing
    Set subfolder = Nothing
    Set folder = Nothing
    Set fso = Nothing

    Range("A1").Select

End Sub

I mentioned that row 1 should have the name of the feature, but it would also have to have the number, because that's what I'm going to use later to add the genres to all the songs using VBA.

Thanks a lot.

 

[arnelgp]

you should expect that not all "attributes" are present on the file.

 

[zelarra821]

I know that.

But since I'm looking for certain attributes, and I'm tired of searching for them one by one, I want to create that table with all the available attributes (name and ID) for each song, so I can find the ones I need.

That's why I'm asking for help with that code, because Excel is freezing and I have to close it.

 

[arnelgp]

try using ChatGPT or CoPilot.
this one is an answer from ChatGPT:

Sub ReadMP3Properties()
    Dim sh As Object, folder As Object, file As Object
    Dim filePath As String, col As Integer
    
    filePath = Application.GetOpenFilename("MP3 Files (*.mp3), *.mp3")
    If filePath = "False" Then Exit Sub
    
    Set sh = CreateObject("Shell.Application")
    Set folder = sh.Namespace(Left(filePath, InStrRev(filePath, "\") - 1))
    Set file = folder.ParseName(Mid(filePath, InStrRev(filePath, "\") + 1))
    
    For col = 0 To 100
        If folder.GetDetailsOf(file, col) <> "" Then
            Debug.Print col & ": " & folder.GetDetailsOf(folder.Items, col) & _
                         " -> " & folder.GetDetailsOf(file, col)
        End If
    Next col
End Sub

 

[isladogs]

See if my free Extended File Properties app does what you want. It will list all the extended file properties for any individual file or all files of a certain type in a folder

https://isladogs.co.uk/extended-file-properties/index.html

 

[zelarra821]

@isladogs, I love you, marry me, please

That's what I needed.

One last thing.

Now that I have the IDs I need, I need a macro that takes the ID value (it can be one or more) for each of those songs from a table I created specifically for that song and adds it to the metadata for those songs.

 

[isladogs]

LOL. Sorry I’m already taken!
Not sure I understand what else you need or why a macro?
As my app stores the data in a table you can either use that directly or append the data you want using a query

 

[zelarra821]

Using your database, I've located the IDs for the metadata I want to add to each of the songs.

Example:

I now have the ID for the music genre.

Okay, so now I need a macro that adds the music genre to each of the songs, taking the value (i.e., the music genre) from a table I created containing the song and the music genre.

Obviously, I don't want the macro to be limited to adding just one metadata item, because I might want to add the year of the song in addition to the music genre.

Do I make myself clear?

 

[arnelgp]

Now that I have the IDs I need, I need a macro that takes the ID value (it can be one or more) for each of those songs from a table I created specifically for that song and adds it to the metadata for those songs.

there is a command-line exiftool.exe to write those attributes/tags.
there Tags that are read-only (no). on this list:

EXIF Tags

sample by ChatGPT:

Sub WriteMetadataWithExifTool()
    Dim ExifToolPath As String
    Dim FilePath As String
    Dim Title As String, Artist As String, Album As String
    Dim Track As String, Year As String
    Dim Command As String
    Dim LastRow As Long
    Dim i As Long
    
    ' Path to ExifTool
    ExifToolPath = "C:\Tools\exiftool.exe"
    
    ' Find last used row in column A
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through rows
    For i = 2 To LastRow
        FilePath = Cells(i, 1).Value
        Title = Cells(i, 2).Value
        Artist = Cells(i, 3).Value
        Album = Cells(i, 4).Value
        Track = Cells(i, 5).Value
        Year = Cells(i, 6).Value
        
        ' Build ExifTool command
        Command = """" & ExifToolPath & """"
        If Title <> "" Then Command = Command & " -title=""" & Title & """"
        If Artist <> "" Then Command = Command & " -artist=""" & Artist & """"
        If Album <> "" Then Command = Command & " -album=""" & Album & """"
        If Track <> "" Then Command = Command & " -track=""" & Track & """"
        If Year <> "" Then Command = Command & " -year=""" & Year & """"
        Command = Command & " """ & FilePath & """"
        
        ' Run command
        Shell Command, vbHide
        
        ' Mark as processed
        Cells(i, 7).Value = "Updated"
    Next i
    
    MsgBox "Metadata updated for " & (LastRow - 1) & " files."
End Sub

 

[cheekybuddha]

or why a macro?

Note we are in the Excel board, so perhaps macro just means VBA here?

 

[isladogs]

@zelarra821
Unfortunately, with the exception of Office file types based on an XML structure such as .xlsx, .docx, .pptx, it is my understanding that you cannot edit the extended file properties of Windows files using VBA. I tried several years ago but only managed to edit properties for the file types listed above.
For music files such as .MP3. the extended properties are read only when working in Access (similarly from Excel I believe)

However, my app saves all of those values to a table:

You could therefore edit the values in the table using either an update query or add new 'properties' using an append query or using equivalent code

Alternatively, use ExifTool (or similar) as @arnelgp has already mentioned. ExifTool is very powerful but its command line based and can be very slow in some situations. I know @CJ_London has investigated using ExifTool in some detail.

 

[zelarra821]

Wow, what a pain!

Then I'll have to add the metadata little by little, because there are more than 2,000 songs that I want to assign a genre and another tag with a personal classification to, but I have to find the metadata to use so it works with foobar2000 and PowerAmp.

Thanks a lot, folks.

 

[isladogs]

If you have that many files to review then I'd consider using ExifTool

 

[zelarra821]

That's what I gonna do