VBA READ/WRITE .MP3 & .WMA PROPERTIES (SOLVED)

[BrianB]

This message has 3 parts - *INTRODUCTION - *READ CODE - *WRITE CODE

INTRODUCTION
I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-
1. READ : Run one macro to put data into a worksheet.....
2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....
3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.
Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.

Open "c:\myfile.mp3" For Binary As #1

etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information http://www.id3.org .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.

READ CODE IS IN THE "REPLY" BELOW

 

[BrianB]

Please read the notes contained in the code more thoroughly.

 

[chefwarren]

Hi Brian,

After reading the notes again.

Starting from scratch and copying the code again.

I still cant get it to work.

Same issues:

1. How do I get it to read every folder in my music folder. It only reads the one I open. I have 1,790 artist folders. (I did find code here(http://www.mrexcel.com/board2/viewtopic.php?t=291736) that opens all the folders and reads the info - can it be combined with your code?)


2. Once information is read it still does not make changes.
It runs through the code. It plays music, then when finished nothing has changed.

Ive read all the notes but still dont get it. Would you mind helping me?

Thanks

Warren


UPDATE Still only reads one folder at a time.

Ran it again. This time folders with one song in them got an error at line:
FilesToChange = ws.Range("A2").End(xlDown).Row - 1 ' count worksheet rows

and when folders with two or more songs are selected got this:

it said summary could not be completed file was being used by another program.

Then one of the songs completely disappeard from the folder.
The one song remaining started to play in WMP. and no change was made to it.

Now I am down one song. Good thing it was only 10cc - Im not in love.

Can anyone help me. I am so lost.

UPDATE: I found the missing file. It was made invisable. Thats a new one.

warren

 

[Jonmo1]

I found this cool code after being inspired by the Van Halen Tour coming to my town. I'M GOING, if I have to beg steal or borrow.....

But anyway, I'm having the same problem as Warren.
It only changes 1 file - The first file in the folder.

But first I'd like to say the READ part of the code is very cool, and works great.

I've also read the instructions carefully. I don't use keyboard or mouse while it's running. And I open explorer (to the folder the files are in) go to file - properties and put the general tab on top. Then run the Write Macro.

It Goes through the motions once on the first file, successfully. Then when going to move onto the 2nd, it's still actually doing the first file.
And in the end only the first file in the folder is changed.

I've also Increased the wait time as suggested.

I don't see any other obvious instrucitons I've missed in the code comments.

Is it maybe a Windows Version Problem? Or something about my folder display setup?

I'm using XP SP2, Excel XP 2002

 

[BrianB]

I have just tried the macro again and it runs Ok on my machine.
It works by taking the file name from Explorer/Properties.. "General" tab and doing 'Find' in the "READ" worksheet. Thus it can ignore any names hidden or removed in the sheet..

As it runs you should hear beeps. The idea is to check synchronisation of code and screen movement.

So to start with you see the Explorer file list, the Properties/General tab for the file opens, then 6 beeps as it goes to the file name with a pause as it is copied to the clipboard and found in the worksheet.

Then the Properties/Summary tab shows, with beeps as the code goes to the top item and selects & processes each one in turn . At present the code only changes Artist,Title,Track Number, Genre, & Title - the rest are skipped.

Hopefully by watching and listening you can see where things get out of synch. It is only achieved by trial and error I am afraid.

There is a subroutine called 'Private Sub WAIT1()' with a 1 second delay that is called on numerous occasions. You could do a search for the single lines calling the routine

WAIT1

and add extra lines with the same code to make the time longer, or go to the subroutine itself and change that. The first method will give more control because 1 second might be enough for most procedures.

Most often the time delays occur when changing dilaog boxes & menus on screen, so you may only need to change only the "Waits" where those are active.

If I can be any further help just let me know.
=========================================
EDIT : from your description,this looks like the bit that needs extra time
(do Find)

        Else
            LastFileChecked = MyFileName    ' name of file just processed
            SendKeys "{DOWN}", True         ' select next file
            WAIT1 
            WAIT1  ' PUT THIS EXTRA LINE *******

 

[Jonmo1]

Thanks for the response Brian.

Hopefully by watching and listening you can see where things get out of synch. It is only achieved by trial and error I am afraid.

Well, it seems to fail at the point after it finishes the first file, then it tries to move on to another file. I see/hear it making the changes to the first file. That is successfull. But it never moves to select the next file in the folder. It just reselects the same one it just changed and goes through it again.

I will try changing the wait time again the way you suggested. Before, I did it by simply changing the Wait1 Macro to wait 3 seconds. Not adding additional Wait1 lines in each individual routine.

But I'm at work now, so I'll have to wait till then to try it.
You don't think it could have anything to do with my Folder Options Setup?
Could you describe how your folder options are set up, and I will duplicate on my PC?

 

[BrianB]

I have Explorer set up as 'Use Windows Classic Folders' with the files laid out using the 'Details' option.

The main point being that the Down Arrow key will select the next file for processing. If he rest is working, it seems that this is the bit that needs attention. Perhaps we need to allow more time for the Properties dialog to close (which is achieved by the final {ENTER} after Comments).

If you PM me your email address I will send screen copies of my Explorer setup. I am using Excel 2000 remember - may be different ?

        Else
            LastFileChecked = MyFileName    ' name of file just processed
            SendKeys "{DOWN}", True         ' select next file
            WAIT1              ' ** ADDED
            WAIT1

 

[Jonmo1]

It works now. Thanks for your help.

Just so Warren Knows, he may be having the same problem I had...

It's definately a PC performance issue. Waiting for the Screen to finish the last keyboard action before beginning the next.

Taking Brian's last suggestion

Else
LastFileChecked = MyFileName ' name of file just processed
SendKeys "{DOWN}", True ' select next file
WAIT1 ' ** ADDED
WAIT1

my actual solution was this

        Else
            LastFileChecked = MyFileName    ' name of file just processed
            WAIT1
            WAIT1
            SendKeys "{DOWN}", True         ' select next file
            WAIT1
            WAIT1

Making it wait 2 seconds BEFORE AND AFTER pressing the DOWN arrow key. That worked for me.

Thanks again for the help Brian. This is VERY Cool Code, Kudos!! Takes forever though, but much easier/better than manually changing the metatag info.

 

[BrianB]

Takes forever though, but much easier/better than manually changing the metatag info.

Story of a programmer's life.

Hence my "cup of coffee" hint below.
Glad you got it working OK.

 

[dannyh]

Hi Guys

I know I must be doing something stupid, when I try and run the first bit of the code, I keep getting the following error.


Compile error:

Method or data member not found


this occurs at the (GetDetailsOf) section of this line

ws.Cells(1, n).Value = MyFolder.GetDetailsOf(MyFolder, MyProperties)

If I remove this section of the code I get the same error at (ParseName) section of this line

Set MyFolderItem = MyFolder.ParseName(MyFileName)


So I am guessing it has something to do with the MyFolder bit of code but don't know what.

Any help would be appreciated

Thanks Danny

 

[jstiene]

I added a shell reference to get past the first error but now get one on the items property in this line:

ws.Cells(1, N).Value = MyFolder.GetDetailsOf(MyFolder.Items, MyProperties(N))