Section 1.4. Kick-Start Lists and XML

1.4. Kick-Start Lists and XML

To see how Excel's list and XML features work, start Excel and follow these steps:

1. Choose File Open, type http://www.mstrainingkits.com/Excel/ExcelObjects.xml , and click OK. Excel asks you how you want to open the file (Figure 1-3).

   Figure 1-3. Opening an XML file in Excel

   
   
   

2. Choose Use the XML Source task pane and click OK. As shown in Figure 1-4, Excel asks if it's OK to create a schema.

   Figure 1-4. Excel offers to do some work for you

   
   
   

3. Click OK, and Excel reads the XML file and generates a view of the data it contains in the XML Source task pane, like that in Figure 1-5.

4. Drag these elements from the object folder to adjacent cells in a row: introduced, topic, name, and docString. Excel creates a list on the worksheet (Figure 1-6).

   Figure 1-5. The XML Source task pane in Excel

   
   
   

   Figure 1-6. An XML-based worksheet list in Excel

   
   
   

5. Choose Data XML Refresh XML Data. Excel downloads the list of Excel VBA objects from the file.

6. 
   

That's pretty neat, but docString is way too long to display on screen. I like using Comments to display multiline text in worksheets and because we're all programmers here, you won't mind writing a little code:

1. Start the Visual Basic editor and open ThisWorkbook in a code window.

2. Select Workbook from code window's the object list and AfterXmlImport in the event list and add the following code:

       Private Sub Workbook_AfterXmlImport(ByVal Map As XmlMap, _      ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)        Dim cel As Range, ws As Worksheet, rng As Range        Set ws = ThisWorkbook.Worksheets("Sheet1")        Set rng = ws.ListObjects("List1").ListColumns("name").Range        For Each cel In rng            If Not (cel.Comment Is Nothing) Then cel.Comment.Delete            cel.AddComment cel.offset(0, 1).Text        Next    End Sub

   
   

3. Return to the worksheet, select the docstring column, and then hide it (Format Column Hide Column).

4. XML Refresh XML Data). After Excel refreshes the list it runs the code you wrote to add comments cells in the name column (Figure 1-8).

   Figure 1-8. Descriptions display as comments after a little code