In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It’s simpler, and faster.
The Worksheet.XmlMapQuery method returns a range of cells mapped to a particular XPath. So, if the element with XPath /Catalog/Book/Title is mapped to a ListColumn on Sheet1 we can find the range of this ListColumn with the command
set rTitles = Sheet1.XmlMapQuery("/Catalog/Book/Title")
Then rTitles.Address will return “$C$3:$C$15”, for example. And obviously, rTitles.XPath will return “/Catalog/Book/Title”.
When I wrote Get XPath objects from Excel workbook a few months ago I was convinced that the XmlMapQuery command would be of no use to retrieve all XPath objects. You would need to know all individual XPaths, and unfortunately they are not easy to retrieve from the schema. However, I recently discovered that we only need the root to obtain all addresses of the mapped ranges! The line
will for example return “$B$8:$B$26,$C$8:$C$26,$D$8:$D$26,$D$4”: the ranges of all mapped elements.
Since the Rootname is available as a property of the XmlMap object, it is not difficult to get a list of all mapped ranges on a sheet and their XPaths:
Dim r As Excel.Range, rXPaths As Excel.Range With ThisWorkbook.XmlMaps(1) Set rXPaths = S01.XmlMapQuery("/" & .RootElementName) For Each r In rXPaths.Areas Debug.Print r.Address; Tab(20); r.XPath Next End With
Note that – although the XmlMap is defined on Workbook level – XmlMapQuery is a worksheet function. So, in case you have mapped your elements to ranges in more than one sheet you must loop through the sheets as well:
Dim ws As Excel.Worksheet Dim r As Excel.Range With ThisWorkbook.XmlMaps(1) Debug.Print .Name; Tab(20); .RootElementName; Tab(40); .RootElementNamespace For Each ws In ThisWorkbook.Worksheets For Each r In ws.XmlMapQuery("/" & .RootElementName).Areas Debug.Print ws.Name; Tab(20); r.Address; Tab(40); r.XPath Next Next End With
An example of a list produced by this procedure:
LeagueMap League urn:pixcels:nl Teams $B$8:$B$26 /League/Teams/Id Teams $C$8:$C$26 /League/Teams/TeamCode Teams $D$8:$D$26 /League/Teams/TeamName Teams $D$4 /League/Season Matches $B$4:$B$310 /League/Matches/Round Matches $E$4:$E$310 /League/Matches/Home Matches $F$4:$F$310 /League/Matches/Away Matches $G$4:$G$310 /League/Matches/For Matches $H$4:$H$310 /League/Matches/Against
Please note that function XmlMapQuery is not perfect. In the above example: when table Teams has one row (listrows.count = 1) then XmlMapQuery(“/League/Teams/Id”) will return $B$8:$B$9. That’s fine. However, when the table is empty (listrows.count = 0) then XmlMapQuery(“/League/Teams/Id”) will also return $B$8:$B$9 (expected: $B$8).
Conclusion: to determine IF an XPath is mapped you should use XmlMapQuery. To determine the mapped data range you use XmlDataQuery. Note that XmlDataQuery returns Nothing when the table is empty AND the XPath is not mapped at all, so you really need XmlMapQuery to determine IF an XPath is mapped.
I hope this pixcel helps. Excel!
Joe Blogger says
Thanks, immensely useful.