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

 

[zelarra821]

Hello.

Following my comment about using ExifTool,

I'm looking at the program, and it turns out it doesn't have a visual interface; everything is done through command lines.

I found a post by a guy who created an interface in 2015, but he hasn't done anything since then, as it was for his personal use, although he later shared it.

Am I using the wrong program?

If so, can you tell me where I can download it?

Thank you very much.

 

[isladogs]

I did say that ExifTool was command-line only back in post #11.
I have no idea about a GUI for ExifTool.

I'll reply to your direct message separately.

 

[KitaYama]

If it was me, I would write a function in vba and use a loop to write a shell command for ExifTool and execute it.
Or if the following fails, I would use vb to write a bat file in a folder in a loop and execute it in each loop.
For example : (not tested)

pth = "C:\Music\"
artist = "Michael Jackson"  
 
Set fileSystem = CreateObject("Scripting.FileSystemObject")
Set files = fileSystem.GetFolder(pth).Files

For Each file In files
   command = "C:\....\exiftool.exe -artist=""" & artist & """ """ & file.Path & """"
   Shell command, vbHide
 Next file

Another option:
mp3tag can batch edit meta data of media files with GUI, Title, artist, album, genre, year, cover art, etc.
Supports actions like renaming files based on tags, or vice versa.

Mp3tag - the universal Tag Editor (ID3v2, MP4, OGG, FLAC, ...)

Mp3tag is a powerful and easy-to-use tool to edit metadata of audio files.

www.mp3tag.de

 

[zelarra821]

Another option:
mp3tag can batch edit meta data of media files with GUI, Title, artist, album, genre, year, cover art, etc.
Supports actions like renaming files based on tags, or vice versa.

That's the option that I'm gonna use

 

[KitaYama]

That's the option that I'm gonna use

I used to use mp3tag. But at some point I stopped using it. I don't remember why, but there was something that I didn't like about it. Or was it a bug?
Test multiple time on test files before working on your actual files.

 

[D_Walla]

As always, I'm painfully late to the party, and given that it's been nearly a month it's probably a safe bet that you've already solved your problem.

But on the off-chance it's helpful to anyone else - it is possible to change the ID3 tags (the MP3 metadata) of a file with VBA. There are two ways that come immediately to mind -

(Method 1) you can change the contents of the file at the byte level (ID3v1 is especially easy in that regard), and then it ramps up in complexity depending on which version of ID3 you want to wrestle with.

(Method 2) What I would suggest however is a VBA project that demonstrates how to read and write enhanced file properties (just like an MP3's genre tag). I am unable to link to it because of [insert rationale here], but you can find it easily on the MrExcel website - the thread is called "Reading and Editing Metadata File Properties" by Jaafar Tribak posted on 26 Feb 2024. On page 2 there is his most up-to-date version of the project.

There is a staggeringly long list of properties you can read and write for all sorts of files, so I'd recommend reading the whole thread.