{"id":55453,"date":"2008-05-31T01:53:00","date_gmt":"2008-05-31T01:53:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/05\/31\/hey-scripting-guy-how-can-i-copy-the-extended-data-of-my-audio-files-to-an-excel-spreadsheet\/"},"modified":"2008-05-31T01:53:00","modified_gmt":"2008-05-31T01:53:00","slug":"hey-scripting-guy-how-can-i-copy-the-extended-data-of-my-audio-files-to-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-copy-the-extended-data-of-my-audio-files-to-an-excel-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Copy the Extended Data of My Audio Files to an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/p>\n

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

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, FB. You know, here at Scripting Guys World Headquarters we always try to go one better. (What\u2019s that? One better than what? Well, you\u2019re not supposed to ask that<\/i> question!) For example, you mentioned the fact that you have a bunch of audio files that are sorted by \u2013 as you put it \u2013 nothing. You\u2019d 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\u2019ll go one step better: we\u2019ll also show you how you can get your script to sort that information on whichever field you choose, absolutely free of charge. We\u2019d like to see you find a better deal than that<\/i>!<\/p>\n

Really? Three for the price of one? You don\u2019t say. Man, that is a way<\/i> better deal than what we\u2019re offering, isn\u2019t 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.<\/p>\n

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 \u201csuperfluous text.\u201d 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\u2019s put it this way: the next time you try to highlight text in Word the application is likely to reply, \u201cSorry, but there doesn\u2019t appear to be any green ink left anywhere in the world.\u201d If that happens, well, now you know why.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. According to the Merriam-Webster Dictionary, superfluous means, \u201cexceeding what is sufficient or necessary; not needed.\u201d Heck, that\u2019s not a description of this column, that\u2019s a description of the Scripting Guy who writes<\/i> this column.<\/p>\n

As everyone in his management chain would be quick to agree.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

But enough of that. We said we had a deal for you, FB, and, boy, do we have a deal for you<\/i>:<\/p>\n

Const xlAscending = 1\nConst xlYes = 1\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n\nobjExcel.Cells(1, 1) = \"Title\"\nobjExcel.Cells(1, 2) = \"Artist\"\nobjExcel.Cells(1, 3) = \"Album Title\"\nobjExcel.Cells(1, 4) = \"Year\"\nobjExcel.Cells(1, 5) = \"Track Number\"\nobjExcel.Cells(1, 6) = \"Genre\"\nobjExcel.Cells(1, 7) = \"Duration\"\nobjExcel.Cells(1, 8) = \"Bit Rate\"\n\ni = 2\n\nSet objShell = CreateObject (\"Shell.Application\")\nSet objFolder = objShell.Namespace (\"C:\\Audio\")\n\nFor Each strFileName in objFolder.Items\n    objExcel.Cells(i, 1) = objFolder.GetDetailsOf(strFileName, 10)\n    objExcel.Cells(i, 2) = objFolder.GetDetailsOf(strFileName, 16)\n    objExcel.Cells(i, 3) = objFolder.GetDetailsOf(strFileName, 17)\n    objExcel.Cells(i, 4) = objFolder.GetDetailsOf(strFileName, 18)\n    objExcel.Cells(i, 5) = objFolder.GetDetailsOf(strFileName, 19)\n    objExcel.Cells(i, 6) = objFolder.GetDetailsOf(strFileName, 20)\n    objExcel.Cells(i, 7) = objFolder.GetDetailsOf(strFileName, 21)\n    objExcel.Cells(i, 8) = objFolder.GetDetailsOf(strFileName, 22)\n    i = i + 1\nNext\n\nSet objRange = objWorksheet.UsedRange\nSet objRange2 = objExcel.Range(\"F1\")\n\nobjRange.Sort objRange2, xlAscending, , , , , , xlYes\n<\/pre>\n
As any good deal should, our deal starts out by defining a pair of constants, xlAscending and xlYes.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How do you know if the used car dealer is really<\/i> offering you the deal of a lifetime? Check and see if, as part of the deal, he\u2019s defining the constants xlAscending and xlYes for you. If he is, then you should buy that car immediately.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
In case you\u2019re wondering, we\u2019re 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<\/i> have a header row.<\/p>\n
After defining the two constants we create an instance of the Excel.Application<\/b> object and then set the Visible<\/b> 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\u2019re going to use these two lines of code to add a new workbook and bind to the first worksheet in that workbook:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/pre>\n
That brings us to this block of code:<\/p>\n
objExcel.Cells(1, 1) = \"Title\"\nobjExcel.Cells(1, 2) = \"Artist\"\nobjExcel.Cells(1, 3) = \"Album Title\"\nobjExcel.Cells(1, 4) = \"Year\"\nobjExcel.Cells(1, 5) = \"Track Number\"\nobjExcel.Cells(1, 6) = \"Genre\"\nobjExcel.Cells(1, 7) = \"Duration\"\nobjExcel.Cells(1, 8) = \"Bit Rate\"\n<\/pre>\n
All we\u2019re doing here is putting column headers in row 1 of our spreadsheet. For example, in the first line we\u2019re assigning cell A1 (that is, row 1, column 1) the value Title<\/i>; in line 2 we\u2019re assigning cell B1 the value Artist<\/i>; and so on. After configuring the header row we assign the value 2 to a counter variable named i, a variable we\u2019ll use to keep track of our current row position in the spreadsheet.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Why do we assign this variable the value 2<\/i>? That\u2019s right: because when we start entering extended data into the spreadsheet we want to start with row 2. (Row 1, as you no doubt recall, contains our column headers.)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
At this point we\u2019re ready to go out and get ourselves some audio files. To do that we first create an instance of the Shell.Application<\/b> object, then use the Namespace<\/b> method to bind to the folder C:\\Audio:<\/p>\n
Set objFolder = objShell.Namespace (\"C:\\Audio\")<\/pre>\n
So then how do we get at all the files that are stored in C:\\Audio? That\u2019s easy; we simply set up a For Each loop that loops us through everything in the folder\u2019s Items<\/b> collection:<\/p>\n
For Each strFileName in objFolder.Items<\/pre>\n
Inside the loop the first thing we do is run smack-dab into several lines of code that look something like this:<\/p>\n
objExcel.Cells(i, 1) = objFolder.GetDetailsOf(strFileName, 10)<\/pre>\n
What we\u2019re doing here is using the GetDetailsOf<\/b> 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\u2019re retrieving item 10, the Title of the audio file.<\/p>\n
OK, a clarification: we\u2019re retrieving the Title as long as this script is running on Windows XP. If you\u2019re running on Windows Vista you\u2019ll be retrieving the name of the file owner; that\u2019s because many of the extended data values on Windows Vista have been renumbered.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Does this mean that the Scripting Guy who writes this column is still running Windows XP? Um, maybe. But if he is, it\u2019s just his way of helping to promote the use of Windows Vista. After all, do you want to upgrade to Windows Vista, or do you want to be like the Scripting Guy who writes that column?<\/p>\n
You know, that\u2019s what everyone says.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
But hey, there\u2019s 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:<\/p>\n\n<\/thead>\n\n\n\n\n\n\n\n\n\n\n
\n
Property<\/b><\/p>\n<\/td>\n
\n
Windows XP<\/b><\/p>\n<\/td>\n
\n
Windows <\/b>Vista<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Title<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
21<\/p>\n<\/td>\n<\/tr>\n
\n
Artist<\/p>\n<\/td>\n
\n
16<\/p>\n<\/td>\n
\n
13<\/p>\n<\/td>\n<\/tr>\n
\n
Album Title<\/p>\n<\/td>\n
\n
17<\/p>\n<\/td>\n
\n
14<\/p>\n<\/td>\n<\/tr>\n
\n
Year<\/p>\n<\/td>\n
\n
18<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n<\/tr>\n
\n
Track Number<\/p>\n<\/td>\n
\n
19<\/p>\n<\/td>\n
\n
27<\/p>\n<\/td>\n<\/tr>\n
\n
Genre<\/p>\n<\/td>\n
\n
20<\/p>\n<\/td>\n
\n
16<\/p>\n<\/td>\n<\/tr>\n
\n
Duration<\/p>\n<\/td>\n
\n
21<\/p>\n<\/td>\n
\n
36<\/p>\n<\/td>\n<\/tr>\n
\n
Bit Rate<\/p>\n<\/td>\n
\n
22<\/p>\n<\/td>\n
\n
28<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Just pick the appropriate column of values and have at it.<\/p>\n
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<\/b>. Because i is currently equal to 2, that means we\u2019re 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.<\/p>\n
So what happens after we\u2019ve added the extended data for all the audio files to our spreadsheet? Well, once we\u2019ve finished with that we then run this block of code:<\/p>\n
Set objRange = objWorksheet.UsedRange\nSet objRange2 = objExcel.Range(\"F1\")\n\nobjRange.Sort objRange2, xlAscending, , , , , , xlYes\n<\/pre>\n
This is where we sort our spreadsheet. We aren\u2019t going to explain spreadsheet sorting in any detail today; after all, we have an entire Office Space article<\/b><\/a> that does that for us. What we will<\/i> say is that we first use the UsedRange<\/b> 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:<\/p>\n
Set objRange2 = objExcel.Range(\"F1\")<\/pre>\n
Why cell F1? Well, we\u2019ve 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.<\/p>\n
Finally we call the Sort<\/b> method, sorting the spreadsheet by Genre, in A to Z order, and taking care to exclude our header row from the sorted data:<\/p>\n
objRange.Sort objRange2, xlAscending, , , , , , xlYes<\/pre>\n
That\u2019s all there is to it.<\/p>\n
And that\u2019s all there is to today\u2019s 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\u2019d rerun this same column on Monday, you know, just in case anyone missed it. However, the Scripting Editor \u2026 persuaded \u2026 us that it might be better to publish a brand-new column on Monday. Thanks, Scripting Editor. Thanks a lot.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! We have quite a large collection of speeches and audio training sessions that are sorted by \u2026 well, by nothing. However, all the ID3 tags [the file\u2019s 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, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,123,49,3,5,192],"class_list":["post-55453","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-multimedia","tag-office","tag-scripting-guy","tag-vbscript","tag-windows-media-player-and-audio"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! We have quite a large collection of speeches and audio training sessions that are sorted by \u2026 well, by nothing. However, all the ID3 tags [the file\u2019s 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, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55453"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55453\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}