|
3.8. Use Templates with XMLSo 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 itTo 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 ordersNow, 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 worksPutting 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:
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 template3.8.3. What about...
|
|