Excel List of All MP3 Files in a Directory

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.

MP3FileLister

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.

__________________

You may also like...

23 Responses

  1. 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.

  2. 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.

  3. Ah! Mystery solved.

    I could make a snide comment about Microsoft here, but I’ll leave that as an exercise for the reader.

  4. Nick Partridge says:

    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.

  5. 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.

    • David Nguyen says:

      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.

  6. tom says:

    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

  7. tom says:

    too bad i can’t spell, lol

  8. Bill Reinehr says:

    Works like a charm – or like magic – take your pick.
    Exactly what I was looking for.

  9. AndyTheGeeky says:

    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.

  10. The Real Mrs. Thelen says:

    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)

  11. Mr Larrington says:

    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

    • Mr Larrington says:

      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…

  12. Wayne says:

    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.

    • Aaron says:

      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.

  13. marjolein says:

    Is it possible to read mp3 files from several folders, without deleting the rest?

    • Mr Larrington says:

      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

  14. Essjay says:

    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!

  15. TamPhan says:

    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!

  16. Inayathullah says:

    What is the label code for Composers?

Leave a Reply to Bill Reinehr Cancel reply

Your email address will not be published. Required fields are marked *