If you have a folder full of MP3 files, you can use Excel to create a list of the files, including title, artist, and song duration.
I pulled together a collection of music for a family wedding, and wanted to share the list with the bride and groom. Rather that write some code myself, I checked a few of my favourite Excel sites.
John Walkenbach has a download that looked perfect for the job. I downloaded his MP3 File Lister, opened the file, and clicked the Start button.
NOTE: John’s site is no longer available, but you can get the MP3 File Lister from the archive of his site on the Wayback Machine site.
Using the MP3 File Lister
I was prompted to select a folder, and a few seconds later, the list was finished. It had included all the files from the top level folder, and all the subfolders.
The Artist and Genre columns were switched, as were the Album and Duration columns. The code is unlocked though, so you could easily change the headings if you encounter the same problem.
The file ran in Excel 2003 and Excel 2007, without any other problems.
__________________
Artist and Genre switched?
That’s odd. I just downloaded it and tried it again. It worked fine. Maybe your MP3s are tagged incorrectly?
To view the tags, right-click an MP3 file, choose Properties, and then look at the Details tab.
please create excel vba file which do work mp3 properties rename
like
NAME,TITLE,ARTIST,ALBUM,YEAR,GENER
No, the properties are fine, and the correct info shows up if I point to a file name in Windows Explorer.
Maybe you’re using Vista, which apparently has different Column IDs.
I’ve got Windows XP on this machine.
Ah! Mystery solved.
I could make a snide comment about Microsoft here, but I’ll leave that as an exercise for the reader.
This works well but somewhere deep in my folder structure there is a file it does not like. Moving the folder with that file away from the tree being searched lets the macro finish. I need to do a bit more detective work to find the “bad” file.
Nick, maybe it’s a special character in the file name that the macro doesn’t like. If you solve the mystery, please let us know.
It IS a special character in the file name that the macro doesn’t like. Has anyone found a way around this? I have alot of music in other languages that I would like to have on this spread sheet. Over 68,000 songs in my library, and this Excel sheet would make searching much more quicker.
Hi David
Did you find a workaround for other languages?
Realize this is in response to an old post, but in 2023, Excel 2010 (but I’ve read ALL versions) VBA still cannot handle characters in Unicode like Thai. I’ve read SOME posts about changing the windows code page but didn’t think that solved the problem. I’m working on a small collection (about 500 files currently – but growing) and getting details like ALBUM will help organize them.
I have found that using PowerShell and changing the codepage [CHCP ###] from 437 to 874 at least makes the file names readable (in Thai) in the terminal window so the output can be copied and pasted or sent to a text file. You can then paste the text into Excel – just can’t use VBA to work on it! I can’t collect the meta data pieces yet. STILL WORKING ON IT THOUGH!
I will retrieve the archived version of the original solution too! Sounds very useful.
Thank you so so much what a great program! boy iahve been looking for this kind of thing for long time. Give kudos to John Walkenbach. alos i found out with this how many songs were tagged incorrectly. Thanks again, Tom
too bad i can’t spell, lol
Works like a charm – or like magic – take your pick.
Exactly what I was looking for.
It isn’t working for me in Office 2010 64-bit on Windows 7.
Even if I change the Declare statements to include PtrSafe, as the error message suggests, Excel just crashes after I click Start.
It sounds amazing, and it seems to be exactly what I was looking for, so I hope there’s some way to make it work.
I have been searching for something like this for a very long time… and it works PERFECTLY!! Thank you very much to John Walkenbach for this MP3 File Lister!!
(I am running Windows 7 Home Premium on an HP Pavilion g7 Notebook x64 based PC with Excel 2010)
1. Anyone know how to persuade it to start in, for example, D:\My Stuffs\My Music\iTunes\iTunes Media\Music rather that in the top-level of the whole system. Would save a lot of clicking, because,
2. It seems to conk out after ~1900 files. Any ideas?
Thanks in advance
Actually, ignore comment re conking out It IS still going – you can use Resource Monitor in W8 to see how far it’s got. If you incorporate the original Clever into a sheet which Does Things with the data, turn off automatic recalculation if you’ve got a lot of files. DAMHIKT…
I like the way it works, even if the headings are switched using XP Pro. What I am not getting is the other information shown in Properties, namely: Bit Rate, Channels and Audio sample rate.
As I am not able to add the necessary code to include these fields, is there a work-around I can use to get this information, or (if you are still watching this post, John) would you consider adding these fields to this sheet? I need this information to help identify which files will have a problem playing in a specific game program I use.
Wayne, after playing around with it, I found a lot of the other labels you were looking for, but could not find all of them. I too was looking for data for album artist (contributing artist is already listed). But the codes are as follows:
1 Type of File
2 Date Modified
3 Date Accessed
4 Date Created
5 Date Modified
6 Attributes
9 Type
10 Owner
11 Type
13 Contributing Artist
14 Album*
15 Year*
16 Genre*
17 Conductors
19 Ratings
20 Artist*
21 Title*
24 Comments
26 Track Number*
27 Duration*
28 Bit Rate
54 Computer
* Already included in the code.
To add the extra columns, you only need to do 2 things, both by stepping into the macros running on the worksheet (“View” Tab, Macros Button, View Macros, Step into “GetAllFiles”; may help to change to “This Workbook” if you have a lot of macros built)
1. Scroll down about half way. Look for:
“Put column headings on active sheet
Cells(1, 1) = “Path”
Cells(1, 2) = “Filename”…
There will be about 8 of these. Copy the last line and paste it to the next line down. Make sure to change the cell number (Ex. 1,8 to 1,9). and change the heading to the desired data (ex. “Filename” to “Bit Rate”
This will add a new column to the spreadsheet
2. Scroll down some more until you see:
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, 21) ‘title…
Again, copy the last line and paste it to a new line. Make sure to change the row number (Ex. “Row, 8” to “Row, 9”).
Change the number at the end of the line (Ex. “, 21” to “, 28” for bitrate; see above list) and the description at the end (Ex. ‘title to ‘bitrate.
Click save and close the macros window. Test your new settings.
Is it possible to read mp3 files from several folders, without deleting the rest?
If you’re asking “Can I list files from folder ‘foo’, then list them from folder ‘bar’, and retain the data you’ve already found for ‘foo'”, then no – it clears everything each time it’s run. Copy all the data for ‘foo’ into a separate sheet and append the stuff from ‘bar’ afterwards if you need to play with it.
HTH
OK, thanks!
I have Windows 8 64-bit and when the spreadsheet opens it says I need to change the Declare statements to include PtrSafe. Can someone tell me how to do this?? Thank you so much!
Hi all!! It runs perfect. I read the code but I can not understand because I am a beginner. I want to edit the name of filename, album, album,… Please give me an advice. Example: I want to edit the name “sample.mp3” to “1.01 sample.mp3 with “1.01” is input by inputbox. Thanks!
I’m sure excel could all that you want (after some work!). But sometimes, it’s just not the best tool for the job. I’ve had good luck with this program: mp3tag
http://www.mp3tag.de/en/
You can apply changes like album name, genre to a bunch of files all at once.
That looks like a handy free tool — thanks Dave!
What is the label code for Composers?
Is the composer available in the Excel list of MP3 files? I am running Windows 7. I know that composer is available in the properties of MP3 files, but I have been unable to find it utilizing Excel.
I have about 35,000 mp3 files that I need to make a list of so would dearly love to get that program.
How do I go about it.
Many thanks
Eamon
down load not available on their site, I have this app with me earlier and it was a perfect tool, missed it if any one have please send link or zip on [removed] Thanks in advance
John’s site is no longer available, but you can get the MP3 File Lister from the archive of his site on the Wayback Machine site.
Hi, did anyone get this script working in 64 bit windows? As previously posted back in 2015 by Essjaysays:
I have Windows 8 64-bit and when the spreadsheet opens it says I need to change the Declare statements to include PtrSafe. Can someone tell me how to do this?? Thank you so much!
I’m running Win10 & would love to get this working, but have limited scripting capability
Thanks in Advance
yes two changes ar required. change the declare functions tothe below and in the get function directory section set x as longPTR
Private Declare PtrSafe Function SHGetPathFromIDList Lib “shell32.dll” Alias “SHGetPathFromIDListA” _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As LongPtr
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As LongPtr
lParam As LongPtr
iImage As Long
End Type
Private Declare PtrSafe Function SHBrowseForFolder Lib “shell32.dll” Alias “SHBrowseForFolderA” _
(lpBrowseInfo As BROWSEINFO) As LongPtr
I have the aplication on an old computer with Vista normally perfectly working with old programs Excel 2003 / 2007 (I still use them and prefer to new versions :)).
However after choosing directory by window it stops (VB 6,3) at
FolderLen = Len(Folder)
With Application.FileSearch — and says that
Application.FileSearch =
How to overrun this? I’m not skilled in vb but used many excel macros and some from Walkenbach page tips years ago and there were very helpful, so I believe it is worth trying. Tell me please if it is possible to make it working?
I have MP3 File Lister on an old computer with Vista normally perfectly working with old programs Excel 2003 amd 2007 (I still use them and prefer to new versions :)).
However after choosing directory by window it stops (VB 6,3) at
FolderLen = Len(Folder)
With Application.FileSearch — and says that
Application.FileSearch =
How to overrun this? I’m not skilled in vb but used many excel macros and some from Walkenbach page years ago and there were very helpful, so I believe it is worth trying. Tell me please if it is possible to make it working?