OfficeTalk: Two Methods for Importing Your Zune Playlist into Excel (Part 1 of 2)

  • Article

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

July 2008

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Setting up the Worksheet

  • Retrieving the Information by using String Manipulation

  • Testing the Project

  • Conclusion

Overview

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.

Setting up the Worksheet

In this section, you create the workbook and add a button to the worksheet.

To setup the workbook to retrieve playlist data

  1. Open a new Excel 2007 macro-enabled (.xlsm) workbook.

  2. On the Developer tab, click Insert, and under Form Controls, click the button control.

    Note

    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.

  3. 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.

  4. 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.

Note

Each track in the My Favorites.zpl file is available in a form similar to the following. <media src="C:&lt;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" />

Retrieving the Information by using String Manipulation

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.

InStr(Starting Position, String Being Searched, Search String, Optional Comparison Type)

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.

Mid(Source, Starting Position, Length)

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.

Testing the Project

In this section, you test the project.

To test the project

  1. Close the Visual Basic Editor.

  2. 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

Conclusion

In this column, you saw how to retrieve song and album information by using String manipulation methods. In part two, you will use treat the file structure as XML and use XPath expressions to retrieve the same information.