• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / XML stuff / Get XPaths from workbook – update

Get XPaths from workbook – update

2013/09/03 by Frans

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

Debug.Print Sheet1.XmlMapQuery("/Catalog").Address

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

Warning

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!

Filed Under: XML stuff Tagged With: xml, XPath

Reader Interactions

Comments

  1. Joe Blogger says

    2018/02/17 at 12:24

    Thanks, immensely useful.

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in