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

Article
05/23/2014

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: In this second installment of a two-part column, see how to create a simple project in Excel 2007 that allows you to import your Zune music playlist into a worksheet by using XML and XPath. (6 printed pages)

Frank Rice, Microsoft Corporation

August 2008

Applies to: Microsoft Office Excel 2007

Contents

Overview
Setting up the Worksheet
Retrieving the Song Information by using XML and XPath
Testing the Project
Conclusion
Additional Resources

Overview

Part one of this series of columns demonstrated how to use String manipulation methods to extract song and album information from a Zune playlist (.zpl) file. In this column, you will see how to treat the file structure as XML and then use XPath to retrieve the information.

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

Open a new Excel 2007 macro-enabled (.xlsm) workbook.
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.
Position the mouse pointer on the right-side of the worksheet leaving at least thee columns visible on the left side of the sheet, 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 in the Button1_Click event method for the button.

Now go to the next section to use XML and XPath expressions 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:<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 Song Information by using XML and XPath

In this section, you add the code to retrieve the playlist information by using XML methods and XPath expressions.

To add the code that uses XML and XPath methods and expressions

In the Button1_Click method, type or paste the following code.

Sub Button3_Click()
Dim xmlDOM As DOMDocument
Dim objNodes As IXMLDOMNodeList
Dim objNode As IXMLDOMNode
Dim objSongNodes As IXMLDOMNodeList
Dim objSongNode As IXMLDOMNode
Dim objArtistNodes As IXMLDOMNodeList
Dim objArtistNode As IXMLDOMNode
Dim objAlbumNodes As IXMLDOMNodeList
Dim objAlbumNode As IXMLDOMNode
Dim objDurationNodes As IXMLDOMNodeList
Dim objDurationNode As IXMLDOMNode
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
Dim FileName As String
Dim numNodes As Integer
Dim i As Integer
Dim r As Integer
Dim c As Integer

r = 0
c = 0

FileName = "C:\VisualStudioProjects\My Favorites.zpl"

' 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

Set xmlDOM = CreateObject("MSXML2.DOMDocument")
xmlDOM.async = False
xmlDOM.Load FileName

Set objSongNodes = xmlDOM.SelectNodes("/smil/body/seq/media/@trackTitle")
Set objArtistNodes = xmlDOM.SelectNodes("/smil/body/seq/media/@trackArtist")
Set objAlbumNodes = xmlDOM.SelectNodes("/smil/body/seq/media/@albumTitle")
Set objDurationNodes = xmlDOM.SelectNodes("/smil/body/seq/media/@duration")

For Each objSongNode In objSongNodes
    If objSongNodes.Length > 0 Then
       ActiveCell.Offset(r + 1, c) = objSongNode.Text
       r = r + 1
    Else
       Exit For
    End If
Next objSongNode

r = 0
For Each objArtistNode In objArtistNodes
    If objArtistNodes.Length > 0 Then
       ActiveCell.Offset(r + 1, c + 1) = objArtistNode.Text
       r = r + 1
    Else
       Exit For
    End If
Next objArtistNode

r = 0
For Each objAlbumNode In objAlbumNodes
    If objAlbumNodes.Length > 0 Then
       ActiveCell.Offset(r + 1, c + 2) = objAlbumNode.Text
       r = r + 1
    Else
       Exit For
    End If
Next objAlbumNode

r = 0
For Each objDurationNode In objDurationNodes
    If objSongNodes.Length > 0 Then
       ' Get the duration of the song (in milliseconds)
       lngTime = CLng(objDurationNode.Text)
       ' 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 variance 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 + 1, c + 3) = strTimeDuration
       r = r + 1
    Else
       Exit For
    End If
Next objDurationNode
End Sub

Looking at the code, the first statements do the same thing as the String example; namely, specifying the location of the .zpl file and setting the column headers. Next, you create an XML Document Object Model (DOM) document and load the data from the playlist file. Then using the XPath expression /smil/body/seq/media/@trackTitle", you load all of the nodes containing the trackTitle attribute. You do the same for the artist, album name, and song duration data.

Next, you loop through all of the trackTitle nodes and assign the text representing the song title to the worksheet column. This process is repeated for the other nodes.

The song duration data is then converted to minutes and seconds in the mm:ss format. You can see more detail of how this is accomplished in the procedures using String manipulation methods in the part one of this series of columns.

Testing the Project

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 file is read and populates the worksheet as seen in Figure 1.

Figure 1. Clicking the button displays the song and album information

Conclusion

Your Zune playlist can be quite expansive. Having that information in a worksheet can be handy as a way to keep a record of the music you have. To make the project even more useful, you might consider adding procedures to write track and album updates back to the .zpl file.

Additional Resources

You can learn more about the topics discussed in this column at the following locations.