Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Viewing and editing a files properties (Excel 2000 SR1)

    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

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing and editing a files properties (Excel 2000 SR1)

    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

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Viewing and editing a files properties (Excel 2000 SR1)

    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.<pre>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</pre>

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing and editing a files properties (Excel 2000 SR1)

    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?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Viewing and editing a files properties (Excel 2000 SR1)

    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. <pre>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</pre>

    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

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Viewing and editing a files properties (Excel 2000 SR1)

    It works like a charm.

    Thanks for your help. It is much appreciated.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Viewing and editing a files properties (Excel

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

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

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Viewing and editing a files properties (Excel

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Viewing and editing a files properties (Excel

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Viewing and editing a files properties (Excel

    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.

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Viewing and editing a files properties (Excel

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> 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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •