Excel 2003 Programming: A Developer's Notebook

Use Templates with XML

So far I’ve worked with XML maps in workbooks. I did this because that’s the way I structured my samples for this book. Actually, XML maps become a lot more useful when stored in Excel templates (.xlt). That way, new workbooks based on those templates automatically contain the XML map and lists you need to display data.

How to do it

To see how this works, create a new workbook and add an XML map, as described previously. Drag items from the XML map onto worksheets as you want them displayed, and save the workbook as a template (.xlt). Figure 3-18 shows a template created for displaying the Order_Map.

Figure 3-18. An Excel template for displaying orders

Now, you can use code to open specific file types using this template. For example, the following code displays a list of .ord files, creates a new Order template, and displays the selected file in the newly created workbook:

     Private Sub cmdOpenOrder_Click(  )
         Dim wb As Workbook
         ' Get a file name to open. Use ".ord" extension for orders.
         Dim fname As String
         fname = Application.GetOpenFilename("Orders (*.ord),*.ord", 1, _
           "Open an Order", "Open", False)
         If fname <> "" Then
             ' Create a new workbook based on the Order template.
             Set wb = Workbooks.Add(ThisWorkbook.Path & "\Order.xlt")
             ' Import the XML data into the existing Map in the template.
             wb.XmlImport fname, wb.XMLMaps("Order_Map")
         End If
     End Sub

Alternately, ...