Excel 2003 Programming: A Developer's Notebook

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:// 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. Click the introduced column heading and select 2003 from the drop-down list. Excel filters the list to display only the new objects (Figure 1-7).

   

   Figure 1-7. A list of the new Excel objects

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. Update the list from the source XML (Data → 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