Section 3.8. Use Templates with XML


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

3.8.1. 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, you could put the code inside of the template Workbook_Open event procedure, so that whenever the user created a new document from the template, he would be prompted for the data file. For example, the following code uses a custom document property to tell whether or not an XML file has been loaded into the templateif XMLSource is False (as when a new workbook is created from the template), then the user is prompted for a file to load:

     ' Template code.     Private Sub Workbook_Open(  )         Dim wb As Workbook         Set wb = ThisWorkbook         If wb.CustomDocumentProperties("XMLSource") = "False" Then             ' 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 <> "False" Then                 ' Import the XML data into the existing Map in the template.                 wb.XmlImport fname, wb.XmlMaps("Order_Map")                 wb.CustomDocumentProperties("XMLSource") = fname             End If         End If     End Sub

3.8.2. How it works

Putting the XML map in a template avoids having to recreate the XML map every time you create a new workbook. The best part is that you can map elements to lists on a worksheet and get the layout and formatting set up the way you want it. Then, the template can prompt for the XML file to open whenever you create a new workbook.

This approach avoids a subtle problem: each XML map can only be used once. There's a one-to-one correspondence between the XML data source, the XML map, and the list object displaying the data. If you want to import multiple XML files through, you either have to use a different map for each file or clear the data binding used by the list object.

If you want to be able to display multiple Orders using the preceding template, you can address these problems by writing code to:

  1. Make a copy of the existing worksheet. This copy saves the data that has already been loaded in the workbook. The new worksheet does not preserve the data bindings from the lists, so once this copy is made it is static.

  2. Change the data bindings on the original worksheet to load new XML data.

The following code illustrates these steps:

      Private Sub cmdOpen_Click(  )          Dim wb As Workbook, ws As Worksheet, fname As String, xmap As XmlMap          Set wb = ThisWorkbook          Set ws = wb.Worksheets("Order")          ' Keep a copy of the Order worksheet.          ws.Copy ws          ' Activate the Order worksheet.          ws.Activate          ' Change data bindings for Order worksheet.          fname = Application.GetOpenFilename("Orders (*.ord),*.ord", 1, _            "Open an Order", "Open", False)          If fname <> "False" Then              Set xmap = wb.XmlMaps("Order_Map")              ' Update the databinding              xmap.DataBinding.LoadSettings fname              ' Refresh the data.                   xmap.DataBinding.Refresh     End If      End Sub

Figure 3-19 shows a workbook based on this template after several XML files have been loaded. Note that the copies are placed before the Order worksheetthis gives the user the impression that the new data is displaying a new worksheet, even though it is actually using the original (data-bound) worksheet.

Figure 3-19. Loading multiple files in an XML template


3.8.3. What about...

To learn how toLook here
Control XML data binding"Get an XML Map from a List or Range"




    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net