|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
OfficeTalk: Two Methods for Importing Your Zune Playlist into Excel (Part 1 of 2)
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Summary: See how to create a simple project in Excel 2007 that allows you to import your Zune music playlist into a worksheet. (7 printed pages)
Frank Rice, Microsoft Corporation
Applies to: Microsoft Office Excel 2007
Zune MP3 players are great devices for storing and playing your song collections. However, Zune music playlists can be very large for the smaller (4GB or 8GB) devices. After adding my songs to the device and scrolling through the playlist, I thought it would be helpful to be able to view my collections in a Microsoft Office Excel 2007 worksheet. This lets me see which songs I have, any misspellings in the song titles, and the length of each song. This information is available in a My Favorites.zpl file that is typically located at C:\Documents and Settings\<Your Alias>\My Documents\Music\Zune\My Playlists.
Note Although the code in this column does not write to the file, I suggest that you make a backup copy of your My Favorites.zpl file.
In this two-part column, I'll describe two custom Visual Basic for Applications (VBA) procedures for retrieving the playlist information and inserting it into an Excel worksheet. The method described in this column uses String manipulation methods to retrieve and display the song and album information. The method described in part two uses XML and XPath to retrieve and display the information. Both methods are initiated from buttons on the worksheet. Each method does the same thing and the choice is yours depending on whether you feel more comfortable working with Strings or working with XML and XPath. I encourage you to try both by adding two buttons to the worksheet and assigning one to the String method and the other to the XML method.
In this section, you create the workbook and add a button to the worksheet.
To setup the workbook to retrieve playlist data
Open a new Excel 2007 macro-enabled (.xlsm) workbook.
On the Developer tab, click Insert, and under Form Controls, click the button control.
If the Developer tab is not visible, you can display it by clicking the Microsoft Office button, clicking Excel Options, and on the Popular tab, selecting Show Developer tab in the Ribbon.
Position the mouse pointer on the right-side of the worksheet, hold down the right-mouse button and draw a rectangle, and then release the mouse button. This places the button onto the worksheet.
In the Assign Macro dialog, click New. This displays the Visual Basic Editor with the cursor in the Button1_Click event of the button.
Now go to the next section to use String manipulation to retrieve the playlist information.
Each track in the My Favorites.zpl file is available in a form similar to the following.
<media src="C:\<path information here>\The Animals - The House Of The Rising Sun.mp3" albumTitle="Best of the Animals" albumArtist="The Animals" trackTitle="The House Of Rising Sun" trackArtist="The Animals" duration="267371"
In this section, you add the code to retrieve the playlist information that uses String manipulation methods.
To add the code that uses string manipulation methods
In the Button1_Click method, type or paste the following code.
Sub Button1_Click() Dim FileName As String Dim txt As String Dim Data Dim r As Integer Dim c As Integer Dim lngTime As Long Dim lngSec As Long Dim lngSec_Remainder As Long Dim lngMin As Long Dim lngMin_Remainder As Long Dim strMinPart As String Dim strSecPart As String Dim strTimeDuration As String FileName = "C:\<Add Your Directory Path Here>\My Favorites.zpl" 'Open the file for reading. Open FileName For Input As #1 r = 0 c = 0 txt = "" ' Format the columns headers. ActiveCell.Offset(r, c) = "Song" ActiveCell.Offset(r, c).Font.Bold = True ActiveCell.Offset(r, c + 1) = "Artist" ActiveCell.Offset(r, c + 1).Font.Bold = True ActiveCell.Offset(r, c + 2) = "Album" ActiveCell.Offset(r, c + 2).Font.Bold = True ActiveCell.Offset(r, c + 3) = "Duration" ActiveCell.Offset(r, c + 3).Font.Bold = True Do Until EOF(1) ' Read a line of data. Line Input #1, Data ' Get the song title and insert it into the worksheet. If InStr(1, Data, "trackTitle=") Then txt = Mid(Data, InStr(1, Data, "trackTitle=") + 12, _ (InStr(InStr(1, Data, "trackTitle=") + 12, Data, """")) - _ (InStr(1, Data, "trackTitle=") + 12)) ActiveCell.Offset(r + 1, c) = txt r = r + 1 End If ' Get the name of the artist. If InStr(1, Data, "trackArtist=") Then txt = Mid(Data, InStr(1, Data, "trackArtist=") + 13, _ (InStr(InStr(1, Data, "trackArtist=") + 13, Data, """")) - _ (InStr(1, Data, "trackArtist=") + 13)) ActiveCell.Offset(r, c + 1) = txt End If ' Get the name of the album. If InStr(1, Data, "albumTitle=") Then txt = Mid(Data, InStr(1, Data, "albumTitle=") + 12, _ (InStr(InStr(1, Data, "albumTitle=") + 12, Data, """")) - _ (InStr(1, Data, "albumTitle=") + 12)) ActiveCell.Offset(r, c + 2) = txt End If ' Get the duration of the song (in milliseconds) If InStr(1, Data, "duration=") Then lngTime = CLng(Mid(Data, InStr(1, Data, "duration=") + 10, _ (InStr(InStr(1, Data, "duration=") + 10, Data, """")) - _ (InStr(1, Data, "duration=") + 10))) ' Convert the song duration from milliseconds to mm:ss format. lngMin_Remainder = lngTime Mod 3600000 ' In the following, you may need to adjust the value ' (from 60000) to compensate for variances in track times. lngMin = lngMin_Remainder / 69000 lngSec_Remainder = lngMin_Remainder Mod 60000 lngSec = lngSec_Remainder / 1000 strMinPart = Format(lngMin, "00") strSecPart = Format(lngSec, "00") strTimeDuration = strMinPart & ":" & strSecPart ActiveCell.Offset(r, c + 3) = strTimeDuration End If Loop Close #1 End Sub
In this procedure, you first assign the path to the My Favorites.zpl file. Next you open the file for reading and then create column headings for each of the data that the code retrieves. Then you read a line of data from the file.
You then test to see if the line of data contains the string trackTitle. If it does, this means that you are in the line of data with the track and album information. The test is performed using the InStr method. The InStr method returns an Integer specifying the start position of the first occurrence of one string within another. The signature for the InStr method is the following.
Returning back to the sample, using a combination of the Mid and InStr methods, you retrieve the title of the song. It might be helpful to examine how this is done in a little more detail.
txt = Mid(Data, InStr(1, Data, "trackTitle=") + 12, (InStr(InStr(1, Data, "trackTitle=") + 12, Data, """")) - (InStr(1, Data, "trackTitle=") + 12))
The trick here is to grab the data between the leading quote mark after trackTitle= and enclosing end-quote mark.
trackTitle="The House Of Rising Sun"
The Mid method has the following signature.
Of course, the Source is the line you just read. You determine the Starting Position by using the InStr method to locate the string trackTitle= and moving the pointer 12 positions to the right; the 12th position is to compensate for the "(quote) after the equal sign. The only thing left is to specify the length you want.
This is done by determining the numeric value of the position just before the closing quote and subtracting the numeric value of the position just after the opening quote to end up with the length of the title text. You repeat the process for the name of the artist, the album name and the duration of the song.
Determining the duration of the song is also very interesting. The value given in the playlist file is in milliseconds or one-thousandth of a second, in this example 267371. This value is retrieved by using the InStr and Mid methods as with the others. And because these methods are String manipulators, the resulting value is also a String. So you use the CLng method to convert the value from a String to a Long Integer. Then using the Mod function and dividing the Integer value by 60 minutes * 60 seconds * 1000 or 3600000 (which is the number of milliseconds in an hour), and then dividing the remainder by 60 seconds * 1000 or 60000, you end up with the duration in minutes. Following a similar logic, you convert the remainder of the duration to seconds. And finally, the values are formatted in the mm:ss style.
In this section, you test the project.
To test the project
Close the Visual Basic Editor.
Place the mouse pointer in cell A1 to make it the active cell and then click the create playlist button. The .zpl is read and populates the worksheet as seen in Figure 1.
Figure 1. Clicking the button displays the song and album information