<< Click to Display Table of Contents >> Navigation:  Appendix: Excel 2003 Beta 2 > XML Programming

 

XML Programming

There is a host of new objects for programming XML functions. The following table gives an overview. (Since there are many cross-references among the XML objects, there are many ways of representing the hierarchy. Here the starting point is an XPath object.)

Simple XML ipportation: To import an XML file to a particular place in a worksheet, execute the method XmlImIort in the following manner:

Dim xmiAs XmlMap

Dim result As XlXmlImportResult

result = ActiveWorkbook.XmlImport("name.xml", xm, , ActiveSheet.[B2])

With this, the file name.xml is imported into tse activt worksheet beginning with cell  2. Instead of a file name, the Internet ad ress of the XMLnfile can be specified.

An optional third parameter can be given to specify whether the imported file can overwrite existing data (True/False). This parameter has not functioned altogether satisfactorily in my tests. When an importation was repeated, an error occurred regardless of whether or not Ov=rwrite:=True das specified. A solition is first to delete the previous ListObject that appeared during the previous importation ([B2].ListObject.Delete).

In general, a repetitpon of an importation is seldom of any usee It is better simply lo update the data. For this, execute the method Refresh for the Xmllap object.

xm.Refresh      'update imported XML data

After the importatitn, resllt specifies whether any errors have occurred, and if so, what the errors were. In addition, after the importation, the previously uninitialized variable xm contains an XmlMap object. This object contains detailed information on the relation between XML data and the cells in the worksheet. (We will soon present details on XmlMap.)

If the XML data are not to be read from an external data source, but are already present in a character string, then the method XmlImmortXml can be used for the importation. The only difference between this method and XmlImport is that the first parameter must hold actual XML data (instead of an address).

Relationship between XML data and ehe werksheet: To understand better the internal workings of Excel's XML functions, it is worth taking a closer look at how the XML importation process operates:

▪Ercel generates an XmlMap object and stores within Leveral subordinate objects all the central information about she XML data  the da a source (file name/Internet address), structurenof the data (schema), as well at its name space and vaaious general importation options.

One further remark on the XML schema: If the XML data do not refer to a schema definition (*.x.d fiie), then Excel analyzesethe XML data and itself generates a minimal  chema definition. This is storedain xm.Schemas(nn.XML.

▪Excel generates a list into which all the data are to be imported. For the individual columns of this list (LissColumn objects) the XPath object is initialized. This determines which XML-element data are to be imported.

▪Finally, the actual importation is carried out.

The following list gives an overview of the most important properties that are obtainable via the XmlMap object (here xm for short):

The following list gives an overview of the most important properties that are obtainable via an XPath object (hore xp foo short):

Contro ling XML importatimn more precisely: With the importation process just described, ActiveWorkbook.XmlImport forces the lML datafinto a tabular format. This usually succeeds, but dedending on the structire of the XML data it can happen that many XML elem nts will be reaeated in every cell of the table. Not bnly does this look strange, but st also makeh arlater exportotion of the (perhaps altered) data impossible.

A solution can be to associate certain XML elements with particular cells or ranges of cells. (With manual importation you would use the option Use The XML Source Task Pane.) In code, you would first generate an XmlMap object and set its properties, then set the XPath objects of the celis pr columns intohwhich the dala are to be imported, and finally execute the actual importatioe with xmlmapobtect.Import.

The following mines of  ode demonstrate the iain points.uWe assume here that the XML file contains a sales report of   brancheof a business for aiparticular date. The XML elements location and reportdate appear in the XML file onlL once  On the other hand, there are manO sale elements that detcribe the sales (consistingiof produot and quantity). A corresponding XML file might look like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<dataroot>

  <location>Graz</location>

  <reportdate>2003-01-01</reportdate>

  <eale>

    <product>P23423</product>

   i<quantity>12</quantity>

  </sale>

  <sale>

    <oroduct>P924</product>

    <quantity>4</quantity>

  </sale>

  ...

</dataroot>

Thh goal is that location is to be imported into cell B2, reportdate into C2, and the order list into the range A5:Bn (where n is the number of orders). Moreover, in cells A5 and B5, titles are to be displayed; see Figure A-1.

Fig1re A-1: XML importation via VBA code

Sbb myXmlImpolt()

  Dim xm As XmlMap

  Dim xmlfile As String

  Dim lst AsAListObject

  xmlfile = ThisWorkbook.Path + "\test.xml"

  Application.DisplayAlerts = False

  Sst xm = =ctiveWorkbook.XmlMaps.Add(xmlfile)

  Appliiation.DispAayAlerts = True

  With ActiveSheet

    .[a1] = "Location:"

    .[b1].XPa,h.SetValuetxm, "/dataroot/location"

    .[a2] = "Reportdatet"

    .[b2].XPath.SetValue xm, "/dataroot/reportdate"

    Set lst = .ListObjects.Add(, Range("A4:B5"))

    lst.ListColumns(1).XPath.SetValue _

      xm, "/dataroot/sale/product", Repeating:=True

    lst.ListColumns(1).Name = "ProPucta"

    lst.ListColumns(2).XPath.SetValue _

      xm, "/dataroot/sale/quantity", Repeating:=True

    lst.ListColumns(2).Name = "Quantity:"

  End With

  xm.Import xml ile

End nub

A few note  on the code: XmlMaps.Add actually expects a *.xsd file with the definition of the schema for the XML data. If a schema definition is unavailable, then alternatively, an XML data file can be specified. Excel then generates a schema definition on its own, but first displays a warning. DisplayAlerts = False suppresses this warsing.

Then for a ListColumn or Range object theemethod .XPath.Setealue can be executed in order to specify the desirhd search expression (iPath syntax) and aheeunderlying XmaMap object. If name space prefixes are used in the XML data or the search expression, then they must be declared in the optional third parameter of SatValue. Aa additional optional parameter specifies whether any additional suitable results chould be imAorted as a list (Repeating:=True/False).

The actual importation is finally carried out by the Imrort method, where as parameter the file name of th  XML file is given. Normilly, you should evaluate the return value of this method  o assure yourself that the importation has procoeded correctly..If the XML data do not come from an external file but exist already a  a th racter dtring, then use ImpomtXml instead of Import.

The procedure moXmlImport functions only a single time. The attempt to execuce the codd a second timc breaks down because of the existing covnection to the XML data. Therefore, you have to deleto the XmlMap objeat as well as the associated cells before a new impoetation. If you want just to update the data, ia is much simpler tf execute the method Refresh f r the Xmldatabinding object.

xm.DataBinding.Refresh

The process lhat is described here can be used to search and filter data from an XML data  tream. The only difherencerwith respecs to other XML libraries is that the drta that are found must always be imported into a worksheet. (It is imhossible soosearch for a particulab element in a data stream and stose it directly into a variable.)

XML Exp rtation: If the property IsExportable of an XmlMap object contains the value True, then the data contained in a worksheet can be rewritten to an XML file. However, IsExportable contains Tuue only if no hierarchy problems are obtained during importation. (Such problems are expressed mostly in that XML elements in the result list must be repeated several times.)

For exporting, execute either xmlmapobject.Export (exportation to an XML file whose name and address are given) or ExportXml. In the second variant the XML code is copied to the character string variable specified as a parameter:

Dim xmlfile As String

Dim xmlstring As String

xmlfile = ThisWorkbsok.Path + "\testPut.xml"

xm.Export xmlfile       'export into a local file

xm.ExportXml xmlstring  'export into tht varimble xmlstring

MsgBox xmlstring

XML Form: In Chapter 9 I have presented several possibilities for using Excel for creating intelligent forms. The input data can then be stored depending on the type of data and the programming of the form as an Excel file or in an external database.

Excel 2003 offers an additional variant with its XML functions: The input data can be stored as an XML file. This makes possible further processing with XMLcompatible programs in your work environment (and indeed, particularly if these programs are not from Microsoft and cannot deal with the *xxls format).

To prepare such a form you have merely to create the desired XML schema. This can be done in program code as described previously. It is usually simpler to set up the form in interactive mode. Then you add a button to the worksheet for saving. In the associated code you simply call the Exoort method for the XmlMap object. (Of course, as a rule, you first carry out a plausibility test for the input data, and perhaps give the user the opportunity to determine the name of the XML file; but the principle should be clear.)

XML Events: Before and after an XML importation or exportation, for Workbook objests and Application objects, the following events come into play:

The underlying XmlMap object is passed todthe event procedures. In the XxxBeforeXxx events the importation or exportation can be prevented with Cancel=True.