MP3 File Info Details

**kiwi_harry** · 10-01-2014, 07:56 PM

I need some help with trying to extract details from MP3 files that I use for work purposes.

I have a library of over 900 MP3 tracks and I found the attached excel file that runs a macro which reads a music folder and extracts details of the MP3 tracks. There is one parameter that I need to extract but I'm unsure what string I need to use. I need to extract the composer details. The file has the following code that extracts the other details I require.

           If UCase(Right(filename, 3)) = "MP3" Then
                Row = WorksheetFunction.CountA(Range("A:A")) + 1
                Cells(Row, 1) = currdir 'path
                Cells(Row, 2) = filename 'filename
                Cells(Row, 3) = FileInfo(currdir, filename, 20) 'artist
                Cells(Row, 4) = FileInfo(currdir, filename, 14) 'album
                Cells(Row, 5) = FileInfo(currdir, filename, 21) 'title
                Cells(Row, 6) = FileInfo(currdir, filename, 26) 'track
                Cells(Row, 7) = FileInfo(currdir, filename, 16) 'genre
                Cells(Row, 8) = FileInfo(currdir, filename, 27) 'duration
                Cells(Row, 9) = Application.Round(FileLen(currdir & filename) / 1024, 0) 'size
                Application.StatusBar = Row

I've tried every number between 1 and 100 after 'filename' but none extract the composer details. I know the composer details exist in the metadata of the file as it lists the composers when I view the MP3 file properties in windows explorer. I tried to attach a short MP3 to this post, but got invalid file.

If anybody can assist me with this then that would be much appreciated.

The original author of the xls file requests on their webpage for no contact for help with their files, but states he is happy for people to freely use the file.

Cheers
Harry

**Richard Buttrey** · 10-01-2014, 08:02 PM

Hi,

It's rather difficult to comment unless we have at least one of the files that contains the data. Please upload that too.

**kiwi_harry** · 10-01-2014, 08:20 PM

Originally Posted by Richard Buttrey

Hi,

It's rather difficult to comment unless we have at least one of the files that contains the data. Please upload that too.

The data is the actual MP3 files (over 900 of them). The macro in the original post attachment opens up an explorer window and I navigate to the folder containing the MP3 files.

**Richard Buttrey** · 10-01-2014, 08:36 PM

Hi,

I understand that but without having one of the files we aren't able to step through the macro in order to work out what needs changing in order to extract the composer details.

**kiwi_harry** · 10-01-2014, 09:25 PM

Ok. I did try and upload a mp3 file but the uploader wouldn't show any of my mp3 files even though all file types was selected. Tried the basic uploader and got invalid file type.

In theory the macro should work on any folder containing MP3 tracks. Obviously you'd need to check that the test file contained metadata with composer information.

If you want I can upload sample mp3 files to dropbox and paste download link here.

**Leith Ross** · 10-01-2014, 10:41 PM

Hello kiwi_harry,

This macro will list all the MP3 with the information you have listed. It creates a header row in row 1 and then lists the files from row 2 down.

Be sure to change the fodler path to where your files are located. It is highlighted in blue font in the macro.

Sub List_MP3_Files()

    Dim Folderpath  As Variant
    Dim Item        As Object
    Dim oFile       As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim r           As Long
    Dim Rng         As Range
    
    
        Range("A1:I1") = Array("Path", "File Name", "Artist", "Album", "Title", "Track", "Genre", "Duration", "Size")
        Set Rng = Range("A2")
        
        Folderpath = "C:\Users\Owner\My Music"
        
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(Folderpath)
        If oFolder Is Nothing Then
            MsgBox "Folder was Not Found", vbExclamation
            Exit Sub
        End If
       
            Set oFile = oFolder.Items
            
            oFile.Filter 64, "*.mp3"
            
            If oFile.Count = 0 Then
                MsgBox "No MP3 Files Were Found in this Folder.", vbExclamation
                Exit Sub
            End If
            
            For Each Item In oFile
                With oFolder
                    Rng.Offset(r, 0) = oFolder
                    Rng.Offset(r, 1) = .GetDetailsOf(Item, 0)
                    Rng.Offset(r, 2) = .GetDetailsOf(Item, 20)
                    Rng.Offset(r, 3) = .GetDetailsOf(Item, 14)
                    Rng.Offset(r, 4) = .GetDetailsOf(Item, 21)
                    Rng.Offset(r, 5) = .GetDetailsOf(Item, 26)
                    Rng.Offset(r, 6) = .GetDetailsOf(Item, 16)
                    Rng.Offset(r, 7) = .GetDetailsOf(Item, 27)
                    Rng.Offset(r, 8) = .GetDetailsOf(Item, 1)
                End With
                r = r + 1
            Next Item
            
End Sub

**kiwi_harry** · 10-02-2014, 03:52 AM

Hi Leith.

That code didn't seem to extract the composer details.

Here is a link to a dropbox folder that contains 2 short MP3 files.
https://www.dropbox.com/sh/tlq390k8u...I0V62ZHOa?dl=0

A snip of the file properties shows the composer information
Track 60 GOML.JPG
Track 208 FD01.JPG

Regards
Harry

Originally Posted by Leith Ross

Hello kiwi_harry,

This macro will list all the MP3 with the information you have listed. It creates a header row in row 1 and then lists the files from row 2 down.

Be sure to change the fodler path to where your files are located. It is highlighted in blue font in the macro.

**Richard Buttrey** · 10-02-2014, 05:17 AM

Hi,

The composer is embedded in the file name string. Try this small adapation of Leith's code which adds the composer as the last column.

Sub List_MP3_Files()

    Dim Folderpath  As Variant
    Dim Item        As Object
    Dim oFile       As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim r           As Long
    Dim Rng         As Range
    
    
        Range("A1:j1") = Array("Path", "File Name", "Artist", "Album", "Title", "Track", "Genre", "Duration", "Size", "Composer")
        Set Rng = Range("A2")
        
        Folderpath = "C:\temp" 'Users\Owner\My Music"
        
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(Folderpath)
        If oFolder Is Nothing Then
            MsgBox "Folder was Not Found", vbExclamation
            Exit Sub
        End If
       
            Set oFile = oFolder.Items
            
            oFile.Filter 64, "*.mp3"
            
            If oFile.Count = 0 Then
                MsgBox "No MP3 Files Were Found in this Folder.", vbExclamation
                Exit Sub
            End If
            
            For Each Item In oFile
                With oFolder
                    Rng.Offset(r, 0) = oFolder
                    Rng.Offset(r, 1) = .GetDetailsOf(Item, 0)
                    Rng.Offset(r, 2) = .GetDetailsOf(Item, 20)
                    Rng.Offset(r, 3) = .GetDetailsOf(Item, 14)
                    Rng.Offset(r, 4) = .GetDetailsOf(Item, 21)
                    Rng.Offset(r, 5) = .GetDetailsOf(Item, 26)
                    Rng.Offset(r, 6) = .GetDetailsOf(Item, 16)
                    Rng.Offset(r, 7) = .GetDetailsOf(Item, 27)
                    Rng.Offset(r, 8) = .GetDetailsOf(Item, 1)
                    Rng.Offset(r, 9) = "=MID(B" & r + 2 & ",FIND(""|"",SUBSTITUTE(B" & r + 2 & ",""-"",""|"",2))+2,FIND(""|"",SUBSTITUTE(B" & r + 2 & ",""-"",""|"",3))-FIND(""|"",SUBSTITUTE(B" & r + 2 & ",""-"",""|"",2))-3)"
                    Rng.Offset(r, 9).Copy
                    Rng.Offset(r, 9).PasteSpecial (xlPasteValues)
                End With
                r = r + 1
            Next Item
            
End Sub

**kiwi_harry** · 10-02-2014, 06:52 AM

Thanks Richard.

That works, but as you indicated the composers name is a string in the filename.

A new music library that I am starting to use doesn't have the composer name in the filename. I've removed the composer names from the files I uploaded to emulate how the new library I'm using presents its tracks.

I don't have an actual sample file from the new library as the portable hard drive is at one of my workplaces. I'm working there on Sunday evening, so can grab a file then to upload if required.

**kiwi_harry** · 10-02-2014, 07:58 AM

Hi Richard and Leith.

I have found the relevant string I need. It was Item, 232.

It works on all of my MP3 files that has composer metadata including commercially available MP3's.

Here is the code.

Sub List_MP3_Files()

    Dim Folderpath  As Variant
    Dim Item        As Object
    Dim oFile       As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim r           As Long
    Dim Rng         As Range
    
    
        Range("A1:I1") = Array("Path", "File Name", "Artist", "Album", "Title", "Track", "Genre", "Duration", "Composer")
        Set Rng = Range("A2")
        
        Folderpath = "Folder Path Goes Here"
        
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(Folderpath)
        If oFolder Is Nothing Then
            MsgBox "Folder was Not Found", vbExclamation
            Exit Sub
        End If
       
            Set oFile = oFolder.Items
            
            oFile.Filter 64, "*.mp3"
            
            If oFile.Count = 0 Then
                MsgBox "No MP3 Files Were Found in this Folder.", vbExclamation
                Exit Sub
            End If
            
            For Each Item In oFile
                With oFolder
                    Rng.Offset(r, 0) = oFolder
                    Rng.Offset(r, 1) = .GetDetailsOf(Item, 0)
                    Rng.Offset(r, 2) = .GetDetailsOf(Item, 20)
                    Rng.Offset(r, 3) = .GetDetailsOf(Item, 14)
                    Rng.Offset(r, 4) = .GetDetailsOf(Item, 21)
                    Rng.Offset(r, 5) = .GetDetailsOf(Item, 26)
                    Rng.Offset(r, 6) = .GetDetailsOf(Item, 16)
                    Rng.Offset(r, 7) = .GetDetailsOf(Item, 27)
                    Rng.Offset(r, 8) = .GetDetailsOf(Item, 232)
                End With
                r = r + 1
            Next Item
            
End Sub

Thanks to both of you for your assistance.

Regards
Harry

**Leith Ross** · 10-02-2014, 11:35 AM

Hello Harry,

Here is a utility macro I created to list the index numbers and file details. They have changed from time to time depending on which version of Windows your are running.

Sub ListFileDetails()

    Dim oFolder As Object
    Dim oShell  As Object
    Dim n       As Long
    Dim Rng As Range
    
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(0)
        
        Set Rng = Range("A1")
        
            For n = 0 To 300
                Rng.Offset(n, 0) = n
                Rng.Offset(n, 1) = oFolder.GetDetailsOf(Nothing, n)
            Next n
                
End Sub

**JBeaucaire** · 10-02-2014, 12:19 PM

@Kiwi - I see you are now using the CODE tags correctly, thanks for that. I've added the missing ones to post #1. Carry on...

**kiwi_harry** · 10-08-2014, 04:27 PM

Thanks Jerry. Apologies for not tagging.

Have my file working and outputting exactly as I want it now.

Cheers

**kiwi_harry** · 08-18-2015, 04:07 AM

Today when I went to update my music file data, found composer information was not being extracted.

Last week I had updated the OS on my PC from Windows 8.1 to Windows 10, so looks like file information has changed between OS versions. After sequentially running through the different item numbers found the new value is now 236.

Updated code is:

Sub List_MP3_Files()

    Dim Folderpath  As Variant
    Dim Item        As Object
    Dim oFile       As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim r           As Long
    Dim Rng         As Range
    
    
        Range("A1:I1") = Array("Path", "File Name", "Artist", "Album", "Title", "Track", "Genre", "Duration", "Composer")
        Set Rng = Range("A2")
        
        Folderpath = "Folder Path Goes Here"
        
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(Folderpath)
        If oFolder Is Nothing Then
            MsgBox "Folder was Not Found", vbExclamation
            Exit Sub
        End If
       
            Set oFile = oFolder.Items
            
            oFile.Filter 64, "*.mp3"
            
            If oFile.Count = 0 Then
                MsgBox "No MP3 Files Were Found in this Folder.", vbExclamation
                Exit Sub
            End If
            
            For Each Item In oFile
                With oFolder
                    Rng.Offset(r, 0) = oFolder
                    Rng.Offset(r, 1) = .GetDetailsOf(Item, 0)
                    Rng.Offset(r, 2) = .GetDetailsOf(Item, 20)
                    Rng.Offset(r, 3) = .GetDetailsOf(Item, 14)
                    Rng.Offset(r, 4) = .GetDetailsOf(Item, 21)
                    Rng.Offset(r, 5) = .GetDetailsOf(Item, 26)
                    Rng.Offset(r, 6) = .GetDetailsOf(Item, 16)
                    Rng.Offset(r, 7) = .GetDetailsOf(Item, 27)
                    Rng.Offset(r, 8) = .GetDetailsOf(Item, 236)
                End With
                r = r + 1
            Next Item
            
End Sub

**dimitris152** · 05-19-2021, 08:17 AM

Originally Posted by kiwi_harry

Today when I went to update my music file data, found composer information was not being extracted.

Last week I had updated the OS on my PC from Windows 8.1 to Windows 10, so looks like file information has changed between OS versions. After sequentially running through the different item numbers found the new value is now 236.

Updated code is:

Sub List_MP3_Files()

    Dim Folderpath  As Variant
    Dim Item        As Object
    Dim oFile       As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim r           As Long
    Dim Rng         As Range
    
    
        Range("A1:I1") = Array("Path", "File Name", "Artist", "Album", "Title", "Track", "Genre", "Duration", "Composer")
        Set Rng = Range("A2")
        
        Folderpath = "Folder Path Goes Here"
        
        Set oShell = CreateObject("Shell.Application")
        
        Set oFolder = oShell.Namespace(Folderpath)
        If oFolder Is Nothing Then
            MsgBox "Folder was Not Found", vbExclamation
            Exit Sub
        End If
       
            Set oFile = oFolder.Items
            
            oFile.Filter 64, "*.mp3"
            
            If oFile.Count = 0 Then
                MsgBox "No MP3 Files Were Found in this Folder.", vbExclamation
                Exit Sub
            End If
            
            For Each Item In oFile
                With oFolder
                    Rng.Offset(r, 0) = oFolder
                    Rng.Offset(r, 1) = .GetDetailsOf(Item, 0)
                    Rng.Offset(r, 2) = .GetDetailsOf(Item, 20)
                    Rng.Offset(r, 3) = .GetDetailsOf(Item, 14)
                    Rng.Offset(r, 4) = .GetDetailsOf(Item, 21)
                    Rng.Offset(r, 5) = .GetDetailsOf(Item, 26)
                    Rng.Offset(r, 6) = .GetDetailsOf(Item, 16)
                    Rng.Offset(r, 7) = .GetDetailsOf(Item, 27)
                    Rng.Offset(r, 8) = .GetDetailsOf(Item, 236)
                End With
                r = r + 1
            Next Item
            
End Sub

Hi! Great work! It's very helpful! How can I find each attribute's number e.g. composer is number 236 so that I can add or remove info at will? Could you send a list? Thanks in advance!

**Richard Buttrey** · 05-19-2021, 09:01 AM

Hi Dimitris and welcome to the forum.

Good to see you've used the Search functionality and found this 7 year old file, not many do.

We like people to start their own threads rather than picking up on existing threads, that can just cause confusion in the middle of a series of questions and answers, particularly where circumstances and data may be slightly different.
In addition those who were parties to the original thread may not be here anymore.

Admittedly neither of those apply in your case, nevertheless can I suggest you start a new thread and include a link to this thread for reference.

**dimitris152** · 05-20-2021, 07:55 AM

Also something else. Is there a way to get excel to let me choose the file via message box instead of giving it a set folder path?

**Richard Buttrey** · 05-20-2021, 08:07 AM

Originally Posted by dimitris152

Also something else. Is there a way to get excel to let me choose the file via message box instead of giving it a set folder path?

As I said. Create your own new thread.

Your 'also something else' demonstrates the point that hijacking a thread with your own special requirement gets confusing and is contrary to our rules.

I don't expect to see any more responses here which address your special variation. Please note.

**macropod** · 05-19-2021, 08:54 AM

See: https://www.msofficeforums.com/159836-post3.html. In addition to outputting all possible attributes, the code there outputs the attribute headers.

MP3 File Info Details

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Re: MP3 File Info Details

Thread Information

Users Browsing this Thread

Similar Threads

VBA code to open many files, copy info, move file to new folder, paste info in second work

Create folder, excel file, pdf file, clear details macro

I would like to prepare a excel containing annual leave details as well as monthly details

How to add Time & Date details in file name using VBA

List file details into a worksheet, is it possible?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions