Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > XML and Excel > XML Validation
Deze pagina in het Nederlands

XML and Excel

XML Validation

Odd enough, validation of XML files against their attached schema definition is turned off by default. Turning on this option will force Excel to check each XML file against the schema during import and export.

First an XML file containing a reference to s schema must be mapped to Excel cells. Close all open files in Excel you don't need and select File, Open. Select file test2-en.xml. I've left out the name of the first employee from this file. Again, select the option "Use the XML source task pane". Drag the branch "employee" to any cell. Next, click the properties button on the table tools tab of the ribbon (External table Data group), See fig. 12.

XML properties button on ribbon
Figure 12: XML properties button on ribbon

For Excel 2003, you can get at the properties dialog by clicking the appropriate button on the "List" toolbar:

The Properties button on the List toolbar in Excel 2003
Figure 13: The Properties button on the List toolbar in Excel 2003

The XML properties dialog opens:

XML Map properties dialog
Figure 14: XML Map properties dialog

Check the box next to "Validate data against schema for import and export".

 Now click the refresh button to import the data from test2_en.xml. The following dialog appears:

Error message caused by importing an XML file that violates its schema
Figure 15: Error message caused by importing an XML file that violates its schema

By clicking the Details... button Excel shows you a bit more information about what is wrong with your XML file (Figure 16). In this case Excel indicates that it did not expect the element "street" to appear yet, but the element "name" (apologies for the partially Dutch screenshot, a bug in Excel's multi-language pack). This error message is caused by the fact that I omitted the name tag and hence this tag is not in the expected position in the XML file. Despite the error message Excel did import the data.

Details of the validation errors caused by the offending XML file
Figure 15: Details of the validation errors caused by the offending XML file

Validation also works when exporting data. Open the file ExcelXML_en.xlsx and remove the name column entirely. Now try to export the XML list (see figure 16)

Rightclick cell menu, XML option, export
Figure 16: Rightclick cell menu, XML option, export

Error message after exporting XML data that violates the schema.
Figure 17: Error message after exporting XML data that violates the schema.


 


Comments

All comments about this page:


Comment by: Mario Radan (4/15/2009 3:18:41 AM)

is it possible to get the xml validation error message through VBA for parsing?

 


Comment by: Jan Karel Pieterse (4/15/2009 3:20:57 AM)

Hi Mario,

As far as I know you can only use the return value of the Import method of the xmlmap object to check if an error has occurred, but not get at the actual error text unfortunately.

If you want to get all details of a schema violation you need to use the Microsoft XML library instead and do the validation using this ActiveX library.

 


Comment by: Ryan (12/2/2010 9:12:31 AM)

I'm trying to change the XSD file that is being used, and then need to remap the elements for data that is already defined on a spreadsheet so that it can be exported as XML correctly. I've been able to determine the logic that will alter the XSD file, but not how to match the elements up with specific columns on the spreadsheet. Would also like the ability to force values to be assoicated during the XML Export that are not defined on the spreadsheet.

Any guidance would be appreciated. Listed below is the code that I'm using to alter the XSD file being used.


Dim curPath, outFile, myXSD As String
Dim xsdCheck As Integer
Dim xogXSD As XmlMap
Dim Xtest As XmlDataBinding
Sub exportXML()
    curPath = "C:\Documents and Settings\rcheek\Desktop\"
    myXSD = curPath & "XOG_RYAN.xsd"
    outFile = curPath & "test1.xml"
    xsdCheck = ActiveWorkbook.XmlMaps.Count
    
    If xsdCheck = 0 Then
        ActiveWorkbook.XmlMaps.Add myXSD, "NikuDataBus"
    End If
    
    Set xogXSD = ActiveWorkbook.XmlMaps("NikuDataBus_Map")

    If xogXSD.IsExportable Then
        ActiveWorkbook.SaveAsXMLData outFile, xogXSD
    Else
        MsgBox "No fields are mapped"
    End If
End Sub

 


Comment by: Jan Karel Pieterse (12/3/2010 6:29:12 AM)

Hi Ryan,

I have posted some sample code here that shows how to map fields to cells:

http://www.jkp-ads.com/Articles/XMLAndExcel05.asp?AllComments=True

Any fields which are not mapped will not be exported I think, so best is to map those fields anyway, possibly in some hidden cells.

 


Comment by: Leisha Cook (3/17/2011 8:52:07 AM)

Do you know of any reason that the last two options in the "XML Map Properties" box (overwrite or append data) would be grayed out? Thanks.

 


Comment by: Jan Karel Pieterse (3/17/2011 9:10:52 AM)

Hi Leisha,

I've seen it happen, but my memory fails me in why it occurs, sorry! Maybe it has to do with the structure of the map itself?

 


Comment by: Emma (12/16/2011 1:37:35 AM)

how do i write a code behind a button in vba to export an data in an excel worksheet to an xml file. pls its very urgent

 


Comment by: Jan Karel Pieterse (12/16/2011 3:45:20 AM)

Hi Emma,

If you insert a button from the Forms controls, you can attache a macro to it by right-clicking the button and selecting Assign Macro.

See www.jkp-ads.com/articles/controls.asp

 


Comment by: Emma (12/16/2011 9:42:10 AM)

pls i need the code to put behind the button in excel
i need to export the data in excel to an xml file. The xml file is supposed to act as a database so subsequent excel data can also be saved in it

 


Comment by: Jan Karel Pieterse (12/18/2011 11:04:47 PM)

Hi Emma,

The first thing I would do is to record a macro whilst manually importing the file. Then study that code and figure out where it needs to be modified to suit your needs.

 


Comment by: kpavan2004 (2/14/2014 9:10:51 PM)

I have a file which contains 10 xml messages.Each XML message is in single line. I would like to import the xml file into excel for analysis. Please suggest the correct approach to import the 10 xml messages into excel

 


Comment by: Jan Karel Pieterse (2/17/2014 7:04:50 AM)

Hi,

Have you tried importing the XML file already? What result do you get?

 


Comment by: alex (3/23/2014 9:42:34 AM)

Using an xml tool would actually be a much easier way of validating your xml and schema.

See; http://www.liquid-technologies.com/FreeXmlTools/FreeXmlValidator.aspx

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].