Results 1 to 10 of 10

Read & Write Mp3 File Tag Properties(solved)

This is a discussion on Read & Write Mp3 File Tag Properties(solved) within the Excel Questions forums, part of the Question Forums category; About a year ago I posted a pair of macros to read/write MP3 tags. The Read macro puts file data ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Read & Write Mp3 File Tag Properties(solved)

    About a year ago I posted a pair of macros to read/write MP3 tags. The Read macro puts file data into a worksheet for making changes, the Write macro uses the worksheet to change the files.The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably.

    Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel. There is a later version 2.0.0.6 but I could not find a download. You might have better success. In "fuzzy" searching for a replacement I found CDDBControlRoxio.dll on my own computer ( I own some of their software) which seems to function the same - except that it actually works. If you have problems with the .dll there seem to be several around with similar names that could be tried. The .dll needs to be registered on your computer by running regsvr32 followed by its file name.

    I have put the READ macro in the next message.
    Code:
    '==========================================================================================================
    '- MACRO TO CHANGE EXTENDED FILE PROPERTIES OF .MP3 AND .WMA FILES IN WINDOWS EXPLORER
    '- Reads from amended worksheet prepared with separate "READ_FROM_EXPLORER" macro module.
    '==========================================================================================================
    '- .WMA files do not have track number column 4
    '- this version uses CDDBControlRoxio.dll
    '- (was unable to get CDDBControl.dll version 1.2.0.51 to change more than 1 file without crashing)
    '- Suggest you copy some files to a special folder for testing first.
    '- Brian Baulsom May 2008  - using Excel 2000/Windows XP
    '==========================================================================================================
    '==========================================================================================================
    '- Method  (works on all files in a single folder)
    '- 1. Run macro "READ_FROM_EXPLORER" (other module) TO GET FILE NAMES INTO CURRENTLY ACTIVE WORKSHEET
    '- 2. Amend file details in the worksheet. Delete rows for files not changed to save time (can be left).
    '- 3. Run macro "WRITE_TO_EXPLORER" below.
    '==========================================================================================================
    '- also uses Public variables in READ macro module
    Dim ws As Worksheet
    Dim FromRow As Long
    Dim LastRow As Long
    Dim FilesToChange As Integer    ' number of files to change
    Dim FilesChanged As Integer        ' number of files changed
    Dim MyFilePathName As String    ' full path & file name
    Dim MyFileType As String        ' mp3 wma etc.
    '-
    Dim id3 As Object
    Dim MyArtist As String
    Dim MyAlbum As String
    Dim MyGenre As String
    Dim MyTrack As String
    Dim MyTitle As String
    '==========================================================================================================
    '- MAIN ROUTINE
    '- Run down visible rows and change data
    '- worksheet has full path & file name in column O
    '==========================================================================================================
    Sub WRITE_TO_EXPLORER()
        Application.Calculation = xlCalculationManual
        Set ws = ActiveSheet
        Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
        '-----------------------------------------------------------------------------------------------------
        '- CHECK NUMBER OF FILES TO CHANGE (VISIBLE ROWS)
        LastRow = ws.Range("A65536").End(xlUp).Row  ' count worksheet rows
        FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count
        If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub
        FilesChanged = 0
        '-----------------------------------------------------------------------------------------------------
        '- LOOP WORKSHEET FILES - VISIBLE ROWS ONLY
        For FromRow = 2 To LastRow
            If ws.Cells(FromRow, "A").EntireRow.Hidden = False Then
                '---------------------------------------------------------------------------------------------
                '- Get file properties from sheet
                With ws
                        MyFilePathName = .Cells(FromRow, "O").Value
                        MyFileType = UCase(Right(MyFilePathName, 3))
                        Application.StatusBar = FileCount & "\" & FilesToChange & " " & MyFilePathName 'STATUSBAR
                        MyArtist = .Cells(FromRow, "B").Value
                        MyAlbum = .Cells(FromRow, "C").Value
                        MyTrack = .Cells(FromRow, "E").Value
                        MyGenre = .Cells(FromRow, "F").Value
                        MyTitle = .Cells(FromRow, "H").Value
                End With
                '---------------------------------------------------------------------------------------------
                '- Write to file
                With id3
                    .LoadFromFile MyFilePathName, False     ' True = Read Only
                    .LeadArtist = MyArtist
                    .Album = MyAlbum
                    .Genre = MyGenre
                    .Title = MyTitle
                    If MyFileType = "MP3" Then .TrackPosition = MyTrack
                    .SaveToFile MyFilePathName
                End With
                '---------------------------------------------------------------------------------------------
                FilesChanged = FilesChanged + 1
            End If
        Next
        '-----------------------------------------------------------------------------------------------------
        '- end of program
        Application.Calculation = xlCalculationAutomatic
        rsp = MsgBox("Done" & vbCr & "Changed " & FilesChanged & " of " & FilesToChange)
        Application.StatusBar = False
    End Sub
    '======= END OF MAIN ======================================================================================
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    This is the READ macro.
    Code:
    '============================================================================================================
    '- READ .MP3 & .WMA FILE PROPERTIES TO A WORKSHEET
    '============================================================================================================
    '- MACRO TO :-
    '- *1. SELECT A FOLDER -  *2. CLEAR THE ACTIVE WORKSHEET*  -  *3. READ .MP3 & .WMA EXTENDED FILE PROPERTIES*
    '- *3. MAKES PROPERTY CELLS BLUE
    '- ONLY EXTRACTS FILE DATA SO CAN BE USED ON ITS OWN. SHEETS CAN BE SAVED AS NORMAL
    '- CAN THEN RUN MACRO "WRITE_TO_EXPLORER" (in another module below) TO *CHANGE* PROPERTIES
    '- Uses Windows Shell32.dll (Requires Tools/References .. 'Microsoft Shell Controls And Automation')
    '- Brian Baulsom July 2007 - using Excel 2000/Windows XP
    ' ==========================================================================================================
    '- Method (works on all files in a single folder)
    '- 1. Run macro "READ_FROM_EXPLORER" below TO GET FILE NAMES INTO CURRENTLY ACTIVE WORKSHEET
    '- 2. Manually amend file details in the worksheet.Delete or hide rows for files not changed saves time(can be left)
    '- 3. Run macro "WRITE_TO_EXPLORER" (other module)
    '===========================================================================================================
    Option Base 1                       ' MyProperties(15) starts 1 instead of 0
    Dim MyFilePathName As String        ' Local variable full path & file name
    Public MyPathName As String         ' **Public variable     |enables 'Sub GetPathFileNameFromFullPath()'|
    Public MyFileName As String         ' **Public variable     |usage in 'WRITE_TO_EXPLORER' macro         |
    '- Properties Array (list of integers)
    Dim Arr1 As Variant                 ' "Name"= shell property zero + First 5 properties in Windows Explorer
    Dim Arr2 As Variant                 ' some more shell GetDetailsOf() property numbers (0-34 available. 3 unused)
    Dim MyProperties(16) As Integer     ' Shell property index numbers used here. Puts them in required order
    Dim MyPropertyNum As Integer        ' Array item position 1-15
    Dim MyPropertyVal As Variant        ' Lookup Array data shell property numbers 0,16, 17 ... etc.
    '-
    Dim ws As Worksheet
    Dim ToRow As Long                   ' write to worksheet row number
    '- Shell variables
    Dim ShellObj As Shell
    Dim MyFolder As Folder
    Dim MyFolderItem As FolderItem
    '-
    '===========================================================================================================
    '- MAIN ROUTINE
    '===========================================================================================================
    Sub READ_FROM_EXPLORER()
        Application.EnableEvents = False    ' WORKSHEET Worksheet_Change() makes changed cells yellow
        '-------------------------------------------------------------------------------------------------------
         '- GET FOLDER NAME FROM FIRST FOLDER\FILE IN THE WORKSHEET
        MyFilePathName = ActiveSheet.Range("O2").Value
        If InStr(1, MyFilePathName, "\", vbTextCompare) <> 0 Then 'there is "\" in the path
            GetPathFileNameFromFullPath (MyFilePathName)    ' PUBLIC SUBROUTINE IN 'READ_FROM_EXPLORER' module
            ChDrive MyPathName
            ChDir MyPathName & "\"
        Else
            ChDrive ThisWorkbook.FullName
            ChDir ThisWorkbook.FullName
        End If
        '- GET FOLDER - Method 1 - using Windows Dialog (comment out if not required)
        'MsgBox ("Selecting a single file in the following dialog gets the required *FOLDER*." & vbCr & vbCr _
                 & "NB. CLEARS THE CURRENTLY ACTIVE SHEET.")
        MyFilePathName = _
            Application.GetOpenFilename("Audio Files (*.mp3;*.wma),*.mp3;*.wma", , "        GET FOLDER REQUIRED")
        If MyFilePathName = "False" Then Exit Sub
        GetPathFileNameFromFullPath MyFilePathName  ' subroutine to separate folder & file name
        '-------------------------------------------------------------------------------------------------------
    '    '- GET FOLDER - Method 2 - hard coded for testing (comment out if not required)
    '    MyPathName = "C:\TEMP\MP3_TEST"            ' SET AS REQUIRED
        '=======================================================================================================
        Set ShellObj = New Shell
        Set MyFolder = ShellObj.NameSpace(MyPathName)
        '------------------------------------------------------------------------------------------
        ChDrive MyPathName
        ChDir MyPathName & "\"
        Set ws = ActiveSheet
        ToRow = 2
        With ws.Columns("A:O").Cells
            .ClearContents                ' clear worksheet
            .Interior.ColorIndex = xlNone
        End With
        ws.Rows.Hidden = False
        '-------------------------------------------------------------------------------------------
        '- INITIALISE PROPERTY ARRAY. CLEAR & SET UP WORKSHEET
        '- Set up array to sort properties into the required order
        '- do not change Arr1 (list of changeable fields in Windows Explorer - used in WRITE macro.)
        '      "Name", "Artist", "Album", "Year", "Track", "Genre", "Lyrics", "Title","Comments")
        Arr1 = Array(0, 16, 17, 18, 19, 20, 27, 10, 14)
            For n = 1 To 9: MyProperties(n) = Arr1(n): Next
        '-     "Duration", "Size", "Date Modified", "Category", "Author", "Bit Rate"
        Arr2 = Array(21, 9, 12, 3, 1, 22, 33)
            For n = 10 To 16: MyProperties(n) = Arr2(n - 9): Next
        '-------------------------------------------------------------------------------------------
        '- write worksheet header
        For n = 1 To 14
            ws.Cells(1, n).Value = MyFolder.GetDetailsOf(MyFolder.Items, MyProperties(n))
        Next
        With ws
            '- "Lyrics" is not included in the Shell properties. I have used a blank one item 27
            .Cells(1, "G").Value = "Lyrics"
            '- This is useful for other purposes.  eg. to play the track via macro.
            .Cells(1, "O").Value = "Full Name"
            .Range("A1:O1").Interior.ColorIndex = 37    ' Dark blue header
        End With
        '===========================================================================================
        '- GET FILE NAMES & PROPERTIES FROM FOLDER
        '===========================================================================================
        MyFileName = Dir(mypath & "*.*")   'first file name
        Do While MyFileName <> ""
            '- filter .MP3 & .WMA
            If UCase(Right(MyFileName, 3)) = "MP3" Or UCase(Right(MyFileName, 3)) = "WMA" Then
                Set MyFolderItem = MyFolder.ParseName(MyFileName)
                '--------------------------------------------------------------------
                '- properties to worksheet
                For MyPropertyNum = 1 To 14
                    MyPropertyVal = MyFolder.GetDetailsOf(MyFolderItem, MyProperties(MyPropertyNum))
                    ws.Cells(ToRow, MyPropertyNum).Value = MyPropertyVal
                Next
                '---------------------------------------------------------------------
                '- add full path\file name (used as lookup by "WRITE_TO_EXPLORER")
                ws.Cells(ToRow, 15).Value = MyPathName & "\" & MyFileName
                ToRow = ToRow + 1
            End If
            MyFileName = Dir    ' Get next file name
        Loop
        '-------------------------------------------------------------------------------------------
        '- finish
        With ws
            .Activate
            '.UsedRange.Columns.AutoFit
            .Range("D1,G1,I1,K1").EntireColumn.Hidden = True
            .Range("A1").Select
        End With
        '-------------------------------------------------------------------------------------------
        '- colour editable range -> blue
        '-------------------------------------------------------------------------------------------
        If ToRow > 2 Then ws.Range("B2:I" & ws.Range("A2").End(xlDown).Row).Interior.ColorIndex = 34
        MsgBox ("Done.")
        Application.EnableEvents = True
    End Sub
    '=========== END OF MAIN ROUTINE ===============================================================
    '===============================================================================================
    '- SUB TO SEPARATE PATH & FILE NAME FROM FULL NAME
    '- puts to Public module level variables 'MyFileName' & 'MyPathName'
    '===============================================================================================
    Public Sub GetPathFileNameFromFullPath(Nm As String)
        For c = Len(Nm) To 1 Step -1
            If Mid(Nm, c, 1) = "\" Then
                MyFileName = Right(Nm, Len(Nm) - c)
                MyPathName = Left(Nm, Len(Nm) - Len(MyFileName) - 1)
                Exit Sub
            End If
        Next
    End Sub
    '----------------------------------------------------------------------------------------------
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    New Member
    Join Date
    Feb 2008
    Posts
    1

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    Brian,

    I am intrigued by this code, but I'm not able to get it to work. Two errors occur for me. When I use the "CDDBControlRoxio.dll" control, the code errors on the line:
    .LoadFromFile MyFilePathName, False

    When I use the "CDDBControl.dll" control,the loop runs successfully, but after the code writes the tags to the file, most tags are blank. A closer look at the files in Windows Explorer shows that the built-in Windows fields in the Details view show the tags as blank (as do the File's Properties window), but if I open the file through the AudioShell Information panel (see http://www.softpointer.com/AudioShell.htm), the tags are written correctly.

    Could the Write_To_Explorer and Read_From_Explorer macros be writing to different metadata fields?

    Thanks,
    Matthew Pfluger

    [IMG]file:///C:/DOCUME%7E1/Pflugs/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    different metadata fields?
    Possibly - although my main criterion for using this was that all changes show in Explorer. This has always been the biggest problem. Although you need to bear in mind that my code does work correctly on both my machines. I use XP Home & XP Professional.
    Have a look at my extended notes in the link below. One thing I have discovered is that changing details manually in Explorer seems to change ALL versions of tag. (a file can contain several Tag versions). I have verified this by viewing mp3 files in a text editor (as far as possible). Tags are really difficult to handle. I tried manipulating the file as text, but gave up.
    VBA READ/WRITE .MP3 & .WMA PROPERTIES (SOLVED)

    1. "Errors out" is no good as a description. What message do you get ? Presumably you have verified that the .dll is present on your disk and has been registered on your system.
    2. The simplest fault here is getting the file name wrong. Double check in debug mode that it has full path, and "\"s are in the right places.
    3. I have tried AudioShell. Thanks for the link. It probably uses the latest IDtag facility to make custom properties. I won't be using this because my existing setup does what I want. The code does not access AudioShell properties. Although it seemed OK on my machine, it may interfere with code on yours.
    Hope this helps.
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    157

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    Hello,

    I've been looking exactly for this or trying to start something along these lines. The first version (first post on the subject) of this seemed to work on the READ, the write though didn't, it seemed to go through the properties and trying to change, but it appeared as the variables weren't being placed in the properties of the mp3file.

    On this newer version, I haven't been able to make anything work eventhough I read the instructions.

    I am getting the following error (I am translating this from the portugues the best I can)
    Error on time of execution '76':
    File path not found.
    When I debug this error I get
    "ChDir ThisWorkbook.FullName" highlighted
    in this variable it seems to have taken correctly the file directory

    I would really want to make it work. Please assist if possible.

    Thank you,
    final

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    The line of code you mention is not critical. All it does is change the default folder. My guess is that you had not saved the workbook -until which there is no path/filename. If you still get the problem I suggest you comment it out. Please always state the error message as well as the code line.

    The problem with mp3 files (and others, especially video) is that there are numerous tag versions - the early ones were just a few basic properties. My main problem was "Genre". Once I had that with Title etc. I was satisfied. Some tag versions do not accept a *custom* Genre.

    I see my message was posted in 2008. Please note that it uses Excel 2000. I have just looked at VB Editor Tools/References in my Excel 2010/Windows 7 setup and see that there are CDDBcontrol references already there. One indicating "Creative" so probably from my sound card. Perhaps these modern ones give better control.

    Also please note that I keep all my personal files on a separate shared hard drive. Because of problems too numerous to mention I reformatted this as FAT32 from NTFS. This means that it will not have access to the latest file properties - which is why it solved my problems. I mention this because it may have a bearing here. I guess unlikely, but you never know.

    I do not have much time to look at this at present, but will do what I can to answer specific queries. Trying to run my old code in this setup is giving me error messages that I do not have time to check.

    If you set Tools/References to include anything containing CDDB and go to the Object Browser (F2 key) you will see that their properties are shown.

    Strange coincidence that I go a PM from diddi today on exactly the same subject. I have directed his attention here so there could be some co-ordination.

    Hope this helps.
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  7. #7
    New Member
    Join Date
    Jan 2012
    Location
    Auckland, NZ
    Posts
    2

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    Hi Brian

    The Kill Devil Hills are a 6 piece country, swampy blues band from Perth, Western Australia, they sing "It's Easy When You Don't Know How"

    I'm interested in your code, but can only get the ID3 Tag Read working

    The .SaveToFile Command cause the following VB Runtime Error on my PC;

    Method 'SaveToFile' of object 'ICddbID3Tag' failed

    I've tried this with both CDDBControl(AOL) and (Roxio) DLL's and got the same result

    I couldn't get all your Code working on my PC, so I stripped it down to the following "Hello World" Code;

    Option Explicit
    Dim MyFilePathName As String
    Dim MyFileType As String
    Dim id3 As Object
    Dim MyArtist As String
    Dim MyAlbum As String
    Dim MyGenre As String
    Dim MyTrack As String
    Dim MyTitle As String

    Sub WRITE_TO_EXPLORER()
    Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
    MyFilePathName = "D:\!T\Aircraft 72 - Make It Allright.mp3"
    MyFileType = "MP3"
    MyArtist = "Artist"
    MyAlbum = "Album"
    MyTrack = "Track"
    MyGenre = "Genre"
    MyTitle = "Title"
    With id3
    .LoadFromFile MyFilePathName, False
    MsgBox .Title
    .LeadArtist = MyArtist
    .Album = MyAlbum
    .Genre = MyGenre
    .Title = MyTitle
    If MyFileType = "MP3" Then .TrackPosition = MyTrack
    .SaveToFile MyFilePathName
    End With

    End Sub

    Any suggestions would be appreciated

    thanks

  8. #8
    KDF
    KDF is offline
    New Member
    Join Date
    Jan 2012
    Posts
    1

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    Thank you,
    this code is great !

    but i'm struggling to put the YEAR in it
    it doesn't show

    can I find somewhere an overview with those property numbers ?

    thank you

  9. #9
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,108

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    MIKEBINZ
    Sorry I don't have time to help much here. I have not done anything with MP3 for ages. It is a bit of a pain, but code That worked well before is giving problems now. I have just done some work automating access to web pages and have 2 machines. Using the exact same workbook & 2010 produces 2 different results.

    One thing that comes to mind using a faster computer and Excel 2010, is that the code can run faster than the computer can do a process, so it is necessary to put delays in to allow time for things to appear on screen or files to process.

    Don't forget to check what file properties show in MS Explorer.

    eg. Always put in a line for such operations after the one that is doing the work :
    DoEvents

    Also, after Doevents, put line delays in the code of 1 second or so such as by using the Sleep function or Application.Wait Now + TimeValue("00:00:01").

    This makes 2 additional lines of code. Try extending the time until it works.
    =====================================================
    KDF
    Here is a list of MP3 Tag info I compiled. It may not be the same as you get. I forget where I got it.
    Code:
    0 Name
    1 Size
    2 Type
    3 Date Modified
    4 Date Created
    5 Date Accessed
    6 Attributes
    7 Status
    8 Owner
    9 Author
    10 Title
    11 Subject
    12 Category
    13 Pages
    14 Comments
    15 Copyright
    16 Artist
    17 Album Title
    18 Year
    19 Track Number
    20 Genre
    21 Duration
    22 Bit Rate
    23 Protected
    24 Camera Model
    25 Date Picture Taken
    26 Dimensions
    27 Not used
    28 Not used
    29 Episode Name
    30 Program Description
    31 Description
    32 Audio sample size
    33 Audio sample rate
    34 Channels
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  10. #10
    Board Regular
    Join Date
    May 2008
    Location
    Biddiston Toowoomba QLD AUS
    Posts
    170

    Default Re: Read & Write Mp3 File Tag Properties(solved)

    'Dim cdx As CDDBCONTROLLibRoxio.CddbID3Tag..... is ok


    At the moment I get errors with ..
    'Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
    'Set id3 = New CDDBCONTROLLibRoxio.CddbID3Tag
    However is there some similar but better dll vba compatable that will
    write back the tags as from namespace folder items to More files
    looking mainly at
    Kind11
    Date taken12
    Year15
    400
    Keywords1013
    MonthMod1014
    FileExt1015
    Folder name176
    Folder path177
    Drive1018
    Path180
    Title21
    Comments24
    Authors20
    Tags18
    Subject22
    People246


    to be writen to any file type... but mainly .xls and . jpg

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com