The code module for the worksheet used to create or edit tests must contain procedures that import and export data between the mapped ranges in the worksheet and the two XML files that describe a test document's properties and its problems.
The first procedure listed is the Click() event of the Command Button control labeled Save File (see Figure 8.11). This procedure exports the data entered in the lists to two separate XML files (the test and test properties files). Both file names are obtained from cell A2 in the worksheet. The test properties file name is appended with a p just before the xml file extension.
When a worksheet already contains an Excel list mapped to an XML document file, you can use the XmlMap object to export the current data from the list to the file. This is exactly what I've done here. I set the XmlMap object variable to its corresponding XML map in the worksheet using the name defined when the XML document was first imported into the worksheet. The names of the XML maps can be found in the XML Source Task Pane (select Data, XML, XML Source). After testing to see if the map is exportable (IsExportable property), I invoked the Export() method of the XmlMap object to export the data from the list to the file specified in the URL argument. With the Overwrite argument set to true, an existing file is replaced with the current data; however, if the file doesn't exist, then a new one is created. This means this event procedure can be used to save new test files or save edits to existing test files.
Finally, because the event procedure involves file I/O, I have added a basic error handler to output the nature of the error to the user via a message box before ending the program.
Private Sub cmdFileSave_Click()
Dim mapProperties As XmlMap, mapTests As XmlMap Dim pathProperties As String, pathTests As String
On Error GoTo ExportError
'Save the new exam as an xml file (one for test properties 'and one for test).
pathProperties = ActiveWorkbook.Path & "\TestProperties\" & Range("A2").Value & "p.xml" pathTests = ActiveWorkbook.Path & "\Tests\" & Range("A2").Value & ".xml" Set mapProperties = ActiveWorkbook.XmlMaps("test_properties_Map") Set mapTests = ActiveWorkbook.XmlMaps("test_Map")
If mapProperties.IsExportable Then mapProperties.Export URL:=pathProperties, Overwrite:=True
MsgBox "XML map is not exportable!", vbOKOnly, "XML Map" End If
If mapTests.IsExportable Then mapTests.Export URL:=pathTests, Overwrite:=True
MsgBox "XML map is not exportable!", vbOKOnly, "XML Map" End If Exit Sub
MsgBox "Test file not saved." & Err.Description, vbOKOnly, _ "File Save Error: " & Err.Number
When the user decides to edit an existing test file, a click of the Command Button labeled Open File sends program execution to its Click() event procedure. I have used this procedure to display a file open dialog containing a list of test property files from which the user must choose one. The data in the selected file, along with the data in its test file counterpart, are then imported into the worksheet. For example, if the user selects the file testlp.xml, its data is imported into the mapped range A2:C2 and the value of its fileID attribute specifies the test file to import into the test_Map range. I have written two custom procedures (GetXMLFile() and OpenXMLFile()) to handle these tasks.
Private Sub cmdFileOpen_Click() Dim fileName As String
'Import xml files to worksheet.
fileName = GetXmlFile If fileName <> "" Then
The GetXMLFile() function procedure uses a FileDialog object (refer to Chapter 7) to display an Open dialog box. I set the file path to the TestProperties directory that contains the test property XML files and added a FileDialogFilters object to ensure the dialog box lists only XML files. The selected file is returned to the calling procedure as a string where it is passed to the OpenXMLFile() procedure. The Open dialog is shown in Figure 8.13.
Private Function GetXmlFile() As String Dim fileDiag As FileDialog Dim fPath As String fPath = ActiveWorkbook.path & "\TestProperties\"
'Configure and show the open dialog. 'Open the file selected by the user.
Set fileDiag = Application.FileDialog(msoFileDialogOpen) With fileDiag 'Configure dialog box .Filters.Clear
.Filters.Add Description:="XML", Extensions:="*.xml", Position:=1 .Filterlndex = 1 .AllowMultiSelect = False .Title = "Select XMl Test File" .InitialFileName = fPath If .Show = -1 Then 'User clicked Open GetXmlFile = .SelectedItems.Item(l) End If End With End Function
The Open dialog for selecting existing test files.
The Open dialog for selecting existing test files.
I added the OpenXMLFile() procedure to a standard code module and gave it public scope because it has to be called from other object code modules in the program. The procedure first uses the string from the fileName argument to import the test properties data from the XML file to the cells in the list. The XmlImport() method of the Workbook object imports the data from the XML document file. It is worth reiterating that the XML document file structure must match the existing XML map structure in the workbook. If the two structures do not match, Excel ignores the command.
Public Sub OpenXMLFile(fileName As String) Dim ws As Worksheet
On Error GoTo ImportError
'Open the test properties and test XML files.
Set ws = Worksheets("Create_Edit_Tests") ActiveWorkbook.Xmllmport URL:=fileName, _
ImportMap:=ActiveWorkbook.XmlMaps("test_properties_Map"), Overwrite:=True ws.Columns("A:C").ColumnWidth = 7
ActiveWorkbook.Xmllmport URL:=ActiveWorkbook.path & _ "\Tests\" & ws.Range("A2").Value & _ ".xml", ImportMap:=ActiveWorkbook.XmlMaps("test_Map"), _ Overwrite:=True Exit Sub
MsgBox "Could not import XML file." & Err.Description, _ vbOKOnly, "File Import Error: " & Err.Number
End End Sub
Was this article helpful?