Viewing and editing a files properties (Excel 2000 SR1)

Topic

New Reply

I am trying to write a small app to help me manage my .mp3 collection. What I would like to do is create a spreadsheet that contains all of a .mp3’s information. I am able to retreive things like file name and file size, but I am having trouble getting the information contained on the “Summary” tab of a files properties.

In other words, I cannot seem to get the artist name, Album title, and other summary information.

Can any one help with this?

Thanks

Reply | Quote

Replies

Mark,
I’m not quite sure what kind of document contains the .mp3’s information, but if its Word or Excel, you can use the CustomDocumentProperties property of the Workbook (in XL) or Document (in Word). Here’s the example out of Excel’s Help:

rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.CustomDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next

Reply | Quote

The information you require is ‘tagged’ on to the end of the mp3 file – see ID3 made easy. Using the information from there, the following code will create a list of mp3 files in a given directory, showing the additional info. The list commences at the active cell.

Public Type mp3Info
    Header As String * 3
    Title As String * 30
    Artist  As String * 30
    Album  As String * 30
    Year  As String * 4
    Comment As String * 30
    Genre  As Byte
End Type
	
Sub Getmp3Info()
Dim mp3ID As mp3Info
Dim fso As Object, fldr As Object, fi As Object
Dim lngRow As Long, lngFile As Long
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("Path") ' Insert directory name
    For Each fi In fldr.Files
        lngFile = FreeFile
        Open fi.Path For Binary As lngFile
        Get lngFile, LOF(1) - 127, mp3ID
        Close lngFile
        If mp3ID.Header = "TAG" Then
            ActiveCell.Offset(lngRow, 0) = fi.Path ' or fi.Name
            With mp3ID
               ActiveCell.Offset(lngRow, 1) = .Title
               ActiveCell.Offset(lngRow, 2) = .Artist
               ActiveCell.Offset(lngRow, 3) = .Album
               ActiveCell.Offset(lngRow, 4) = .Year
               ActiveCell.Offset(lngRow, 5) = .Genre
               ActiveCell.Offset(lngRow, 6) = .Comment
            End With
            lngRow = lngRow + 1
        End If
    Next
    Set fso = Nothing
    Set fldr = Nothing
End Sub

Andrew C

Reply | Quote

That is perfect. Thank You for you help.

I have been trying to edit the information using VBA, but I haven’t had any luck. Is it possible to edit the information using VB or VBA? If so, can you point me in the right direction? (FYI, I am trying to Synchronize all of my artist, album and genre information with having to go into each song individually)

Also is there any way to return the Bit Rate and Duration?

Reply | Quote

Mark

If you include the following with the previous code, you can update the MP3 info from the spreadsheet. When you read in the data, if you alter any of the details like Artist, Album etc and run the following it should update the file. Just select the cell with the first filename to edit and run the code. Do not change the pathfilename. Hopefully it will get you started. This is very dependant on the previous code, so any changes you made need to applied here as well.

Sub WriteMp3Info()
Dim mp3ID As mp3Info
Dim lngFile As Long
Dim rngFiles As Range
Dim oCell As Range
    Set rngFiles = IIf(ActiveCell.Offset(1, 0) = "", ActiveCell, _
                       Range(ActiveCell, ActiveCell.End(xlDown)))
    For Each oCell In rngFiles
        With mp3ID
            .Header = "TAG"
            .Title = oCell.Offset(0, 1)
            .Artist = oCell.Offset(0, 2)
            .Album = oCell.Offset(0, 3)
            .Year = oCell.Offset(0, 4)
            .Genre = oCell.Offset(0, 5)
            .Comment = oCell.Offset(0, 6)
        End With
        lngFile = FreeFile
        Open oCell.Text For Binary As lngFile
        Put lngFile, LOF(1) - 127, mp3ID
        Close lngFile
    Next
End Sub

There must be a way to return the bit rate and duration etc, but this data is stored in the mp3 file proper, and is not included in th e Tag info. Will need to study up on the mp3 file format and header layout before offering any suggestions, unless some Lounger has experience of the format.

Andrew C

Reply | Quote

It works like a charm.

Thanks for your help. It is much appreciated.

Reply | Quote

[indent]

---

There must be a way to return the bit rate and duration etc, but this data is stored in the mp3 file proper, and is not included in th e Tag info. Will need to study up on the mp3 file format and header layout before offering any suggestions, unless some Lounger has experience of the format.

---

[/indent]

Does anyone know of a way to retrieve bit rate, duration and track number?

Reply | Quote

I think getting the bit rate would be complicated; apart from anything else, the bit rate can vary throughout an mp3 file so I doubt it is stored as information in any one location. If you can get the duration and the file size then you can do an average.

Reply | Quote

In XP MCC (and I think all XP flavors) if you select an MP3 file and get Properties | Summary | Advanced, there is an “Audio” section which indicates the duration, bitrate, channels, and sample rate. But looking at the same file with a Hex Editor doesn’t show any of that data in decimal string format.

Reply | Quote

John Walkenbach has a freely downloadable workbook that creates a list of MP3 files with details such as duration and bitrate: Excel Developer Tip: Creating A List of MP3 Files. The VBA code in the workbook is not protected, so you can study it to see how it works.

Reply | Quote

Amazing… I feel like I’ve just spent a couple of days reinventing the wheel [or a way to extract mp3 data from specific directories into an Excel spreadsheet]! Thanks for the link

Reply | Quote