Excel's XML SOURCE window showing heirarchy - Need to transfer or extract it's contents ASAP

[ChrisOK]

As a long time member of MrExcel, I'm hoping all the Aladin Akyurek genius-types of folks can provide some desperately needed help -- I've been searching for weeks and not finding a *single soul* that knows anything on this topic or it's Excel functionality which seems shocking!?!

With client deadline fast approaching and knowing that it's sometimes hard to visualize; I recently posted on 2 sites that have the ability to upload a sample pic file - to better explain..
Cross-post link disclaimer is available if seeing the uploaded visual is helpful:
https://www.excelforum.com/excel-pr...336-extract-heirarchy-from-source-window.html
http://www.msofficeforums.com/excel-programming/37789-extract-heirarchy-source-window.html

The image shows the several tiers of the Parent Child relationships within the EXCEL XML SOURCE window. (there's at least 6 tiers)

I simply need everything that's on this list to be layed into a spreadsheet or document.
Ideally, if it could mirror the architecture that'd be great to see visually at a glance how many layers deep an element lies...

Heck, I'll even take it in a hard to read layout like:
Level.........Name........
1..............Abc (which is obviously the parent as a '1')
2..............Bbc (level 2 is a child to the parent listed above it)
3..............Ccc (level 3 here is a child to the child)
1..............Ddd
2..............Eee
1..............
2..............
3..............
4..............
5..............
6..............

Ultimately the client needs to see (with as much ease as possible) a full list of everything in that map... to gauge magnitude... next to see how many parents there are -- and determine how many levels inward they want us to go as a dev team -- to tackle issues within a system.

When I select all, copy & paste everything in that XML Source window, (paste it long ways along row one of a sheet) -- it holds over 8,000 items
I'm finding that it is leaving out the PARENTS which is a critical piece...
So, I don't have any idea HOW MANY items are REALLY within that XML Source window because what got pasted is NOT all inclusive! Frustrating!

The main 4 things I need are the PARENT, REQUIRED PARENT, CHILD and REQUIRED CHILD (based on the icons shown in the XML Source window, those are the 4 items I need a list of)

Need help with some VBA or a method for grabbing everything in that window and pasting it either into an Excel sheet - -or even into Word if that's easier --

Any ideas how to pull out what's being displayed visually within the Excel files XML Source window?
Thanks in advance -- praying SOMEONE -- ANYONE knows something about this window and how to get what's in it --- OUT!
Chris

 

[ChrisOK]

no idea why that image was green -- sorry about that -- here's a replacement link: https://app.box.com/s/a0t23blxfubop7lhshvshqnhk55722wu
I'll continue to read through the rest - while you get a chance to see the image more clearly - I hope!

 

[ChrisOK]

UPDATE!!! <<<<<<<<<<<<<<<<<
OH MY GOSH----------IT WOOOOOOOOOOOOOORKED!!!!!!!!!!!!!
HAPPPPPPPPPPY DANCE!! JOHN IS AMAZING! -- NOSPARKS too for helping!

Sooooooo freakin' excited!
THANK YOU -- THANK YOU --- THANK YOU! THIS IS AWESOME!!
And THANK YOU for breaking parts apart to explain it's functionality -- that really makes more sense!

I changed the code to ActiveSheet as suggested -- great idea -- never have to worry about sheet conflict..

So, if I take this code and put it into a new file and suck in the other file called: "PRDS_v3.0", is the file name reference the only thing I need to worry about changing to make this re-usable with different .xsd files?

 

[ChrisOK]

I just tested it with the 3.0 file and changed that one file refc and it WORRRRRRKS!
Man --- you have no idea how grateful I am! This is truly awesome code! Thank you again!

 

[John_w]

I'm pleased that you finally got it working!

So, if I take this code and put it into a new file and suck in the other file called: "PRDS_v3.0", is the file name reference the only thing I need to worry about changing to make this re-usable with different .xsd files?

Yes, just edit the code to change the file name to be loaded, or specify the full folder path and file name. However, rather than editing the 1st macro every time you want to process a different file, I have changed the code to allow you to browse to and select a .xsd or .xml file.

Another change is that both macros now operate on the active workbook. This means you can have just one workbook containing the code (.xlsm file) and run the macros from another open workbook. The macros will then extract the XML tree hierarchy from the selected file or the internal schema into the active sheet in the active workbook.

Finally, I've changed the names of the macros slightly to more accurately describe their function. These two macros replace the two main macros previously posted and uploaded, and go with the Scan_ElementsToCells code.

Option Explicit

Public Sub Extract_XML_File_Structure()
    
    'Requires reference: Microsoft XML v6.0
    
    Dim XMLfile As String
    Dim XMLdoc As DOMDocument60
    Dim XMLmainNode As IXMLDOMNode
    Dim destCell As Range
    Dim rowOffset As Long
    
    'Browse for .xsd or .xml file
    
    With Application.FileDialog(msoFileDialogOpen)
        .Title = "Select XSD or XML file"
        .AllowMultiSelect = False
        .InitialFileName = ActiveWorkbook.Path
        .Filters.Clear
        .Filters.Add "XML Schema Definition files", "*.xsd"
        .Filters.Add "Extensible Markup Language files", "*.xml"
        If .Show Then
            XMLfile = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    Set XMLdoc = New DOMDocument60
    'Set XMLdoc = CreateObject("MSXML2.DOMDocument.6.0") 'for late binding
    
    With XMLdoc
        'The PDS_Schema_v2.5.1.xsd file contains the following schema:
        '< xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="07112016">
                
        'How To Specify Namespace when Querying the DOM with XPath - https://support.microsoft.com/en-us/help/294797
        
        .async = False
        .validateOnParse = False
        .SetProperty "SelectionLanguage", "XPath"
        .SetProperty "SelectionNamespaces", "xmlns:xs='http://www.w3.org/2001/XMLSchema'"
        .Load XMLfile
    End With
    
    With ActiveWorkbook.ActiveSheet
        .Cells.ClearContents
        Set destCell = .Range("A1")
    End With
    
    'Get the main document node, < xs:element name="ProcurementDocument">.  All data elements are contained in this node
    
    Set XMLmainNode = XMLdoc.SelectSingleNode("//xs:element [@name='ProcurementDocument']")
    
    If Not XMLmainNode Is Nothing Then
        Scan_ElementsToCells XMLdoc, XMLmainNode, destCell, rowOffset, 0
        MsgBox "Finished parsing XML file " & XMLdoc.URL & vbNewLine & vbNewLine & _
               "Last row written to = " & destCell.Row + rowOffset - 1
    Else
        MsgBox "Unable to find xs:element with name 'ProcurementDocument' in XML schema file " & XMLdoc.URL
    End If
    
End Sub


Public Sub Extract_XML_Internal_Structure()
    
    'Requires reference: Microsoft XML v6.0
    
    Dim XMLinternalSchema As XmlSchema
    Dim XMLdoc As DOMDocument60
    Dim XMLmainNode As IXMLDOMNode
    Dim destCell As Range
    Dim rowOffset As Long
    
    On Error Resume Next
    Set XMLinternalSchema = ActiveWorkbook.XmlMaps(1).Schemas(1)
    On Error GoTo 0
    If XMLinternalSchema Is Nothing Then
        MsgBox "The workbook '" & ActiveWorkbook.Name & "' doesn't contain an XML schema"
        Exit Sub
    End If
    
    Set XMLdoc = New DOMDocument60
    'Set XMLdoc = CreateObject("MSXML2.DOMDocument.6.0") 'for late binding
    
    With XMLdoc
        'The .xsd file contains the following schema:
        '< xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified" version="07112016">
                
        'How To Specify Namespace when Querying the DOM with XPath - https://support.microsoft.com/en-us/help/294797
        
        .async = False
        .validateOnParse = False
        .SetProperty "SelectionLanguage", "XPath"
        .SetProperty "SelectionNamespaces", "xmlns:xs='http://www.w3.org/2001/XMLSchema'"
        .LoadXML XMLinternalSchema.XML
    End With
    
    With ActiveWorkbook.ActiveSheet
        .Cells.ClearContents
        Set destCell = .Range("A1")
    End With
    
    'Get the main document node, < xs:element name="ProcurementDocument">.  All data elements are contained in this node
    
    Set XMLmainNode = XMLdoc.SelectSingleNode("//xs:element [@name='ProcurementDocument']")
    
    If Not XMLmainNode Is Nothing Then
        Scan_ElementsToCells XMLdoc, XMLmainNode, destCell, rowOffset, 0
        MsgBox "Finished parsing internal XML schema '" & XMLinternalSchema.Name & "' in XML map '" & XMLinternalSchema.Parent.Name & "'" & vbNewLine & vbNewLine & _
               "Last row written to = " & destCell.Row + rowOffset - 1
    Else
        MsgBox "Unable to find xs:element with name 'ProcurementDocument' in internal XML schema '" & XMLinternalSchema.Name & "' in XML map '" & XMLinternalSchema.Parent.Name & "'"
    End If
        
End Sub

 

[ChrisOK]

Fantastic improvements it sounds like -- but sadly, having probs getting it to run... (you're like - OH GOD - not again!?!) - yeah, me too.. ha
Here's the steps I performed -- was it me - or the code that caused the fault?

1. Created new XLSM file, named it "Testing-NEW-code-Wed-011018" (thinking this new code does not need to have the new file name edited within the code - right?)
2. Checked the XML 6.0 property
3. Inserted NEW Module (left it named as Module1) - (ran a search for "modDOMdocument" in the code and saw no refc - so figured I don't need to rename new module - right?)
3a. Copy/Pasted the most recent code window and that's it -- if more from someplace else needed added - then not sure what (put all in one window if more is needed to the most recent code)
4. Did NOT perform the 'Developer tab > SOURCE > XML Maps > Add' step (sounds like I don't need to do that anymore and can just navigate to the XSD file when prompted - but not getting prompt)
5. Did NOT change the sheet name (left as default "Sheet1") - is that correct or should I have manually defined it in the code?
5. Ran the code and this was the result: https://app.box.com/s/0lgjn4w50pm3br8y4e8vh56ou4tf226h
* note: both the PDS and PRDS files are sitting within the same folder as this test file -- of which I had planned to navigate to when the prompt arises

 

[John_w]

The error is Sub or Function not defined, with Scan_ElementsToCells highlighted.

Remember this bit?

Finally, I've changed the names of the macros slightly to more accurately describe their function. These two macros replace the two main macros previously posted and uploaded, and go with the Scan_ElementsToCells code.

Therefore copy and paste the Scan_ElementsToCells subroutine (everything from the Private Sub line to End Sub inclusive) previously posted into the module containing the 2 main macros. (It doesn't matter that the module is named Module1.)