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]

Ok, I'm happy to insert that code -- - but where exactly shall I insert -- I'm not an expert w/ this stuff--- but can place it where you indicate... after..... :

 

[John_w]

The single new line goes inside the Sub Extract_XML_Document_Structure, between the lines "Set XMLmainNode = ..." and "Scan_ElementsToCells XMLdoc, XMLmainNode, destCell, rowOffset, 0".

Maybe this is clearer:

    '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']")
   [COLOR=#ff0000][B] If XMLmainNode Is Nothing Then Stop  'New line[/B][/COLOR]
    
    Scan_ElementsToCells XMLdoc, XMLmainNode, destCell, rowOffset, 0

 

[ChrisOK]

ok - here ya go -- no idea what this means - but here's where it stopped and no error box appeared --
https://app.box.com/s/qce9ifqj34nhbu2a3cxj39959h036tbz

 

[John_w]

That confirms where the error is occurring. The code stops on that new line because the preceding line can't find the main ProcurementDocument element. If you press the F5 key to continue running the code it will stop again at the line and with the error you originally said.

I'm guessing here (not sure of the syntax for SelectSingleNode string argument), but try changing the line to one of these:

    Set XMLmainNode = XMLdoc.SelectSingleNode("//xs:element[@name='ProcurementDocument']")   'either this
    Set XMLmainNode = XMLdoc.SelectSingleNode("//xs:element[@name=""ProcurementDocument""]")  'or this

 

[NoSparks]

John's original code worked for me with no problems.

Are you using a workbook that has been saved in the same folder you have the .xsd file in ?

 

[ChrisOK]

Yes - as noted previously -- a new file was saved into the same location as the xml file (other files were also present -- so I went and created a new folder, new xlsm file and placed the xml into this new file where there is nothing else in that file folder-- other than this attached xlsm file and the xml file.

I added the 2 new lines of code and commented out each one --- only allowing one active at a time -- and got the same results each time -- hopefully, that outcome tells something?
(please see attached file to verify code is in the right place and looks the way you wanted it)
https://app.box.com/s/atnpuz9eea4d0dmnwama8438vkws1hhb

Here's the outcome of each: (the same line below was highlighted in blue):
https://app.box.com/s/7stv3w9gkl3q87yw5lmeaty2m4ea11lz

1st new line of code result:
https://app.box.com/s/bmitepycq32jtx345mp4dinmrzdmbb4o

2nd new line of code result:
https://app.box.com/s/yde6bx2xnh2dq0glxypw73kx0n4bgtgl

ohhhhh so close --- what can it be -- odd that all 3 react the same outcome (the last 2 images don't show the blue highlighted line - but in all 3 instances the same line was blue)
Hope this helps?

 

[NoSparks]

Worked for me with your file after deleting sheet xlSheetVeryHidden "Acerno_Cache_XXXXX"

 

[ChrisOK]

Wow What tha' Heck!?!
Did this sheet get generated when the XML add map occur?
And how odd that it's hiding another one ---
Image: https://app.box.com/s/6dsz4n0sbxmigv812rihhnkmk1wiomne
- ok I see that "Acerno" sheet within the VBA window - but obviously not from the front end
When I click VIEW > Unhide from the front end the only thing listed is the PERSONAL area..
When I click HOME > Format > Hide/Unhide (the unhide sheet is grayed out) --
When I right click on the "Acerno" sheet from the backend (VBA window) to see if there's a delete option -- not seeing one --
So, is using VBA the only way one can get rid of that one? and when I do --- does the other one that's NOT visable on the front end -- become visible?

How do I get rid of the one you mention is the apparent problem?

 

[NoSparks]

Actually it works even with that sheet, you just can't see it and your fonts are very very small when you do make it visible.
Which makes me wonder why the code is giving you issues.
The .xsd file I have, and which it would be working with on my computer, is downloaded from the link you provided earlier.

Anyway to delete the very hidden sheet...
In the VBA environment, click on Sheet1(Acerno_Cache_XXXXX)
click the properties icon, look at Visible at the bottom of the properties list.
Set it to visible then you can delete it.

 

[ChrisOK]

ok -so I guess deleting the sheet is not the solution -- if you're saying it now works whether the sheet is deleted or not..
I deleted it, saved, closed and reopened and it was BACK! It's like it's generated upon open if not found...
Next time I deleted it, saved then ran the code while it was confirmed gone -- (took this screen shot of the result) -- same blue line gets highlighted and same "user-defined type not defined" error..
https://app.box.com/s/hyejcjog5eib64gvina0i8xnzr7g3ca9

I also tried selecting all, increasing font to extremely large and making it black on both pages -- never could see any font?
Clicked on various cells and then looked up within the formula bar for text and nothing --- so not sure how/where exactly you're seeing small font (it must be generated because the code is somehow working for you -- and as a result, you see font generated)
Do you not ever see a "UsER DEFINED TYPE NOT DEFINED" error?
=-( This is frustrating... what in the world could need to change --