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

    Extracting MP3 Data (Excel XP)

    A while back, Andrew posted the following code which I could use successfully to extract mp3 data from the mp3tags in a specified directory. The code would result in a nice spreadsheet with the data neatly arranged in columns.

    Since then, I have changed the method in which I store the mp3 files. So the code needs to be tweaked. Can someone please help with the code? My mp3's are stored in the following manner: Cocuments and SettingsRickyMy DocumentsMy Music ArtistNameAlbumName*.mp3

    The current code works great when all mp3's are stored in one directory. But I need a way for it to search all sub directories of
    Cocuments and SettingsRickyMy DocumentsMy Music.....

    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")
    <font color=red>Set fldr = fso.GetFolder("Path")</font color=red> <font color=448800> ' Insert directory name </font color=448800>
    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
    - Ricky

  2. #2
    New Lounger
    Join Date
    Jun 2003
    Location
    Australind, Western Australia, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting MP3 Data (Excel XP)

    Hi Tricky

    You can use the File Search Property for this. See the example below

    Sub DoesWorkBookExist()
    '''''''''''''''''''''''''''''''
    'Written by www.Ozgrid.com

    'Test to see if a Workbook exists
    ''''''''''''''''''''''''''''''''
    Dim i As Integer

    With Application.FileSearch
    .LookIn = "C:MyDocuments"
    .SearchSubFolders = True
    '* represents wildcard characters
    .Filename = "Book*.xls"
    If .Execute > 0 Then 'Workbook exists
    MsgBox "There is a Workbook."
    Else 'There is NOt a Workbook
    MsgBox "The Workbook does not exist"
    End If
    End With
    End Sub

  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: Extracting MP3 Data (Excel XP)

    Ricky,

    Try

    Sub Getmp3Info()
    Dim mp3ID As mp3Info
    Dim fso As Object, fldr As Object, sfldr As Object, fi As Object
    Dim lngRow As Long, lngFile As Long, strPath As String
    strPath = "Cocuments and SettingsRickyMy DocumentsMy Music"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder(strPath)
    For Each sfldr In fldr.SubFolders
    For Each fi In sfldr.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
    Next
    Set fso = Nothing
    Set fldr = Nothing
    End Sub

    If any of the folders contain files other rthan MP3s, a further tweak may be required,

    Andrew

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting MP3 Data (Excel XP)

    This code will ONLY look in SUBFOLDERS of the main folder. It will NOT recurse through all the sub-sub folders and ethe sub-sub-sub folders, etc. If that is all the is desired, this is fine. But if you want to "dig deeper" thru the folders this code might suit better

    Steve

    <pre>Option Explicit
    Dim fso As FileSystemObject
    Dim strPath As String
    Dim result As Boolean
    Dim iPathLen As Integer
    Dim lngRow As Long

    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()
    Set fso = CreateObject("Scripting.FileSystemObject")
    strPath = "Cocuments and SettingsRickyMy DocumentsMy Music"
    iPathLen = Len(strPath) + 1

    lngRow = 0

    result = ExtractMP3Data(strPath)

    Set fso = Nothing
    End Sub

    Private Function ExtractMP3Data(fspec)
    Dim mp3ID As mp3Info
    Dim fldr As Folder, fi As File, sfldr As Folder
    Dim lngFile As Long
    Set fldr = fso.GetFolder(fspec)
    On Error Resume Next
    If fldr.Files.Count <> 0 Then
    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
    End If

    If fldr.subfolders.Count > 0 Then
    For Each sfldr In fldr.subfolders
    ExtractMP3Data (sfldr)
    Next
    End If

    ExtractMP3Data = True
    Set fldr = Nothing
    End Function</pre>


  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: Extracting MP3 Data (Excel XP)

    Steve,

    Thanks for your scrutiny. I did overlook searching subfolders within subfolders.

    The following coe should cover take care of this, but uses the FileSearch object, rather than the FileSystem object.

    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 FindMP3Info()
    Application.ScreenUpdating = False
    Dim oFSearch As FileSearch
    Dim varFile As Variant
    Dim mp3ID As mp3Info
    Set oFSearch = Application.FileSearch
    With oFSearch
    .NewSearch
    .LookIn = "Cocuments and SettingsRickyMy DocumentsMy Music"
    .Filename = "*.mp3"
    .SearchSubFolders = True
    If .Execute() > 0 Then
    For Each varFile In .FoundFiles
    lngFile = FreeFile
    Open varFile For Binary As lngFile
    Get lngFile, LOF(1) - 127, mp3ID
    Close lngFile
    If mp3ID.Header = "TAG" Then
    ActiveCell.Offset(lngRow, 0) = varFile ' 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
    End If
    End With
    Application.ScreenUpdating = True
    End Sub

    Neither method will prove especially fast, so processing thousands of files would take some time. If speed is an issue, using gthe API may be the best solution.

    Your code as posted requires that a reference be set to Scripting Runtime.

    Andrew

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

    Re: Extracting MP3 Data (Excel XP)

    Thanks guys for your responses. I appreciate the new code. Just FYI - I used the code from Andrews last post and it took 3:36 seconds to pluck 3,983 mp3 files from my folders. I only mention this as speed was mentioned in the dialogue. It doesn't matter to me, the code worked just as I had hoped and served my purpose.

    Thanks again,
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - 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
  •