XML and Excel

Pages in this article

XML Schemas

An XML file can have a schema definition (XSD) attached to it. A schema definition determines the structure of the XML file. Other things an XSD file may define are:

The format of the data inside an element (string, number, etcetera)
Whether or not an element is mandatory
If an element can be repeated.

To attach an XML file to a schema a reference to the schema file must be included inside the XML file. The schema reference can be added as an attribute of the root element, like this:

The text in Bold builds the reference to the schema file schema.xsd, which in this example must reside in the same folder as the xml file. Without getting into the details of the exact syntax of a schema definition here is an example tied to the file test1_en.xml:

Listing 1: Schema.xsd

<?xml version='1.0' encoding='UTF-16'?>
 <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <xsd:element name="companies">
       <xsd:complexType>
          <xsd:sequence minOccurs="0">             <xsd:element minOccurs="0" name="company" >                <xsd:complexType>
                   <xsd:sequence minOccurs="0">                      <xsd:element minOccurs="0" type="xsd:string" name="companyname" />                      <xsd:element minOccurs="0" maxOccurs="unbounded" name="employee" >                         <xsd:complexType>                            <xsd:sequence minOccurs="0">                               <xsd:element minOccurs="1" type="xsd:string" name="code" />                               <xsd:element minOccurs="1" type="xsd:string" name="name" />                               <xsd:element minOccurs="0" type="xsd:string" name="street" />                               <xsd:element minOccurs="0" type="xsd:string" name="Houseno" />                               <xsd:element minOccurs="0" type="xsd:string" name="areacode" />                               <xsd:element minOccurs="0" type="xsd:string" name="place" />                               <xsd:element minOccurs="0" type="xsd:string" name="phone" />                            </xsd:sequence>                         </xsd:complexType>
                      </xsd:element>
                   </xsd:sequence>
                </xsd:complexType>
             </xsd:element>
          </xsd:sequence>
       </xsd:complexType>
    </xsd:element>
</xsd:schema>

Such a schema is obviously less easy to read than the accompanying XML file. More information on schemas.

Extensible Stylesheets

With Extensible Stylesheets (xsl files) xml files can be converted to other formats, e.g. to SpreadsheetML, the XML format that supports spreadsheets. How these stylesheets work is outside the scope of this article. More information about extensible stylesheets by the w3c organisation .

Comments

All comments about this page:

Comment by: Leemo Tsheboagae (31-3-2010 21:15:53) deeplink to this comment

What are the disadvantages of XML schema?
How is an XML schema generated using UML models?

Comment by: Jan Karel Pieterse (31-3-2010 22:00:19) deeplink to this comment

Hi Leemo,

To be honest, I have not used UML at all yet. I found a discussion on XML and UML here:

http://www.ibm.com/developerworks/library/x-umlschem/

Comment by: JD (28-11-2012 01:08:56) deeplink to this comment

I hope you are still offering assistance with VBA script...

I created my XSD Schema described above, but now I'm trying to figure out the VBA code to map it to a 2010 Excel worksheet, before I import my XML data. I've looked through Google and cannot find anyone with knowledge of this code.

Workbooks.Add
ActiveWorkbook.XmlMaps.Add("c:\VendorDetails.xsd", "vendordetails").Name = "vendordetails_Map"
ActiveWorkbook.XmlMaps("vendordetails_Map").Import URL:="c:\VendPaymtDtl.xml"

Please let me know.

Thank you,

JD

Comment by: Jan Karel Pieterse (28-11-2012 08:45:10) deeplink to this comment

Hi JD,

Check out this page, it contains a comment showing just how you do that:

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

Comment by: M.J. Boes (30-1-2019 14:56:52) deeplink to this comment

Dear Jan Karel,

I use XML schemas to keep my Excel models 'refreshable'. A model upgrade takes place by extracting the end-user data from the current model and import it in the new version. Simple.
However, in my latest model the tables contain formula which results should be exported for reporting purposes as well. In the MS documentation the following is advised:

Unmap the elements that you don't want overwritten, before you import the XML data. After you import the XML data, you can remap the XML element to the cells containing the formulas, so that you can export the results of the formulas to the XML data file.

Create two XML maps from the same XML schema. Use one XML map for importing the XML data. In this "Import" XML map, don't map elements to the cells that contain formulas or other data that you don't want overwritten. Use another XML map for exporting the data. In this "Export" XML map, map the elements that you want to export to an XML file.

The second approach does not fit my goal as a cell cannot be mapped to two schemas, and for some reason I cannot find a routine to automate the first approach anywhere. Do you have or know such a routine?

I've written one - which I include in a separate form due to the length - but I'd prefer to use proven code if available.

Thank you in advance,

Marc

Comment by: M.J. Boes (30-1-2019 14:57:56) deeplink to this comment

# ThisWorkbook

Dim oXMLImport As clsXmlImport

Private Sub Workbook_AfterXmlImport(ByVal map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)
oXMLImport.AfterImport map
Set oXMLImport = Nothing
End Sub

Private Sub Workbook_BeforeXmlImport(ByVal map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)
Set oXMLImport = New clsXmlImport
oXMLImport.BeforeImport map
End Sub

# class module clsXmlImportCellClass

Option Explicit

Public rRange As Range
Public sXPath As String
Public sXPathMap As String

Comment by: M.J. Boes (30-1-2019 14:58:34) deeplink to this comment

# class module clsXmlImport

Option Explicit

Private colUnique As Collection, bXMLPanelVisible As Boolean

Public Sub BeforeImport(map As XmlMap)
Dim ws As Worksheet, rCell As Range, oCellXpath As clsXmlImportCellClass
bXMLPanelVisible = Application.CommandBars("XML Source").Visible
If bXMLPanelVisible Then Application.CommandBars("XML Source").Visible = False
'*** Read collection with the (first) cells connected to the Map ****
For Each ws In ThisWorkbook.Worksheets
For Each rCell In ws.UsedRange
    If rCell.XPath = "" Or Not rCell.HasFormula Then
    ElseIf rCell.XPath.map = map.Name Then
     Set oCellXpath = New clsXmlImportCellClass
     Set oCellXpath.rRange = rCell
     oCellXpath.sXPath = rCell.XPath
     oCellXpath.sXPathMap = map.Name
     On Error Resume Next
     colUnique.Add oCellXpath, rCell.XPath
     On Error GoTo 0
     Set oCellXpath = Nothing
    End If
Next
Next
'*** Unmap cells
For Each oCellXpath In colUnique
oCellXpath.rRange.XPath.Clear
Next
End Sub

Public Sub AfterImport(map As XmlMap)
Dim oCellXpath As clsXmlImportCellClass
If Not colUnique Is Nothing Then
For Each oCellXpath In colUnique
    If oCellXpath.sXPathMap = map.Name Then
     oCellXpath.rRange.XPath.SetValue map, oCellXpath.sXPath
    End If
Next
End If
If bXMLPanelVisible Then Application.CommandBars("XML Source").Visible = True
End Sub

Private Sub Class_Initialize()
Set colUnique = New Collection
End Sub

Private Sub Class_Terminate()
While colUnique.Count > 1: colUnique.Remove 1: Wend
Set colUnique = Nothing
End Sub

Comment by: Jan Karel Pieterse (2-2-2019 16:11:14) deeplink to this comment

Hi Marc,

Thanks! I'm afraid I haven't got much to add to your code.

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.