Hey, Scripting Guy! How Can I Copy the Extended Data of My Audio Files to an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! We have quite a large collection of speeches and audio training sessions that are sorted by … well, by nothing. However, all the ID3 tags [the file’s extended data] are up-to-date. Is there a way to extract the ID3 information from these files into an Excel spreadsheet?
— FB

Hey, FB. You know, here at Scripting Guys World Headquarters we always try to go one better. (What’s that? One better than what? Well, you’re not supposed to ask that question!) For example, you mentioned the fact that you have a bunch of audio files that are sorted by – as you put it – nothing. You’d like to be able to extract all the ID3 information (which is all the extended data for the file, things like the artist name, track number, etc.) and then write that information to an Excel spreadsheet. Can we help you with that? You bet we can. And then we’ll go one step better: we’ll also show you how you can get your script to sort that information on whichever field you choose, absolutely free of charge. We’d like to see you find a better deal than that!

Really? Three for the price of one? You don’t say. Man, that is a way better deal than what we’re offering, isn’t it? You wonder how they can do stuff like that and still stay in business. Looks the Scripting Guys have some shopping to do this afternoon.

Of course, we should also point out that some of you are wondering how the Scripting Guys manage to stay in business; as far as most people can tell, all we do is write a daily scripting column that never seems to get around to talking about scripting. For example, we recently received an email from a reader who appreciated the script we gave everyone, but who pointed out that the column itself included some “superfluous text.” To help illustrate his point, he highlighted all the superfluous column text in green. Was there a lot of superfluous text for him to highlight? Let’s put it this way: the next time you try to highlight text in Word the application is likely to reply, “Sorry, but there doesn’t appear to be any green ink left anywhere in the world.” If that happens, well, now you know why.

But enough of that. We said we had a deal for you, FB, and, boy, do we have a deal for you:

Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1) = "Title"
objExcel.Cells(1, 2) = "Artist"
objExcel.Cells(1, 3) = "Album Title"
objExcel.Cells(1, 4) = "Year"
objExcel.Cells(1, 5) = "Track Number"
objExcel.Cells(1, 6) = "Genre"
objExcel.Cells(1, 7) = "Duration"
objExcel.Cells(1, 8) = "Bit Rate"

i = 2

Set objShell = CreateObject ("Shell.Application")
Set objFolder = objShell.Namespace ("C:\Audio")

For Each strFileName in objFolder.Items
    objExcel.Cells(i, 1) = objFolder.GetDetailsOf(strFileName, 10)
    objExcel.Cells(i, 2) = objFolder.GetDetailsOf(strFileName, 16)
    objExcel.Cells(i, 3) = objFolder.GetDetailsOf(strFileName, 17)
    objExcel.Cells(i, 4) = objFolder.GetDetailsOf(strFileName, 18)
    objExcel.Cells(i, 5) = objFolder.GetDetailsOf(strFileName, 19)
    objExcel.Cells(i, 6) = objFolder.GetDetailsOf(strFileName, 20)
    objExcel.Cells(i, 7) = objFolder.GetDetailsOf(strFileName, 21)
    objExcel.Cells(i, 8) = objFolder.GetDetailsOf(strFileName, 22)
    i = i + 1
Next

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("F1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes

As any good deal should, our deal starts out by defining a pair of constants, xlAscending and xlYes.

In case you’re wondering, we’re going to use both of these constants when we go to sort our spreadsheet. xlAscending tells the script to sort the data in ascending (A to Z) order, while xlYes tells the script that yes, as a matter of our spreadsheet does have a header row.

After defining the two constants we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. And what are we going to do with this new instance of Excel? Well, for starters, we’re going to use these two lines of code to add a new workbook and bind to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

That brings us to this block of code:

objExcel.Cells(1, 1) = "Title"
objExcel.Cells(1, 2) = "Artist"
objExcel.Cells(1, 3) = "Album Title"
objExcel.Cells(1, 4) = "Year"
objExcel.Cells(1, 5) = "Track Number"
objExcel.Cells(1, 6) = "Genre"
objExcel.Cells(1, 7) = "Duration"
objExcel.Cells(1, 8) = "Bit Rate"

All we’re doing here is putting column headers in row 1 of our spreadsheet. For example, in the first line we’re assigning cell A1 (that is, row 1, column 1) the value Title; in line 2 we’re assigning cell B1 the value Artist; and so on. After configuring the header row we assign the value 2 to a counter variable named i, a variable we’ll use to keep track of our current row position in the spreadsheet.

At this point we’re ready to go out and get ourselves some audio files. To do that we first create an instance of the Shell.Application object, then use the Namespace method to bind to the folder C:\Audio:

Set objFolder = objShell.Namespace ("C:\Audio")

So then how do we get at all the files that are stored in C:\Audio? That’s easy; we simply set up a For Each loop that loops us through everything in the folder’s Items collection:

For Each strFileName in objFolder.Items

Inside the loop the first thing we do is run smack-dab into several lines of code that look something like this:

objExcel.Cells(i, 1) = objFolder.GetDetailsOf(strFileName, 10)

What we’re doing here is using the GetDetailsOf method to retrieve extended data from the first file in the folder. (Or, more correctly, from the file whose path is currently stored in the variable strFileName.) And what extended datum are we retrieving here? Well, in line 1 we’re retrieving item 10, the Title of the audio file.

OK, a clarification: we’re retrieving the Title as long as this script is running on Windows XP. If you’re running on Windows Vista you’ll be retrieving the name of the file owner; that’s because many of the extended data values on Windows Vista have been renumbered.

But hey, there’s no need to panic. The following table shows you the name of each property we used in the script, along with the appropriate value in both Windows XP and Windows Vista:

Just pick the appropriate column of values and have at it.

Now, where were we? Oh, right, we use GetDetailsOf to retrieve the value of the Title property, then we assign that value to cell i, 1. Because i is currently equal to 2, that means we’re talking about the cell in row 2, column 1; in other words, cell A2. We then repeat this process for all the other property values. After incrementing our counter variable by 1 we go back to the top of the loop and do it all over again, this time with the second file in the collection.

So what happens after we’ve added the extended data for all the audio files to our spreadsheet? Well, once we’ve finished with that we then run this block of code:

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("F1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes

This is where we sort our spreadsheet. We aren’t going to explain spreadsheet sorting in any detail today; after all, we have an entire Office Space article that does that for us. What we will say is that we first use the UsedRange property to select all the cells in our spreadsheet that contain data. We then use the line of code to create a teeny-tiny Excel range consisting solely of cell F1:

Set objRange2 = objExcel.Range("F1")

Why cell F1? Well, we’ve decided to sort our spreadsheet by Genre, which happens to be the data listed in column F. In order to sort a spreadsheet programmatically on column F we need to select a single cell somewhere in column F. Cell F1 seemed to be as good as any.

Finally we call the Sort method, sorting the spreadsheet by Genre, in A to Z order, and taking care to exclude our header row from the sorted data:

objRange.Sort objRange2, xlAscending, , , , , , xlYes

That’s all there is to it.

And that’s all there is to today’s column. Will we be back on Monday with another deal similar to this one? Well, to tell you the truth, we originally planned to be back on Monday with this same exact deal: we thought we’d rerun this same column on Monday, you know, just in case anyone missed it. However, the Scripting Editor … persuaded … us that it might be better to publish a brand-new column on Monday. Thanks, Scripting Editor. Thanks a lot.

ScriptingGuy1

Follow