I'm quite new to all this XML and VBA. I'm honestly doing everything based on my knowledge of oop, but the current project I am working on requires to be done in VBA.
I've searched everywhere but I can't find and can't think of any practical way to do what I must do right now, so I am asking for everyone's knowledge in here for help if it's possible.
So basically I have an XML file that is something like this:
<a name="something" >
<b name="something">
<c>
<d>number1</d>
<e>number2</e>
<f>
<g>number3</g>
<h>number4</h>
</f>
</c>
</b>
</a>
My problem is that I need to generate the contents of the XML in an Excel worksheet. I do know how to do that, however, what I end up with is something like:
something
something
number1 number2 number3 number4
number1
number2
number3 number4
number3
number4
What I want is to somehow get rid of the lines in italics.
I'm using recursion within sub procedures because that's how it is required. However, when I check for child nodes and for the node value to be printed, I end up with all the child nodes and their values of the nodes that do not have a particular value themselves.
I know I could skip over the nodes manually using the baseName but this is not what I'm looking for. If it's possible to make a function of it's own or something more general that could apply to any cases of this happening, would be nice.
Thank you for the help!
EDIT: (The code I have for now)
Sub Main()
Dim XDoc As MSXML2.DOMDocument
Set XDoc = New MSXML2.DOMDocument
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Clear
Dim point As IXMLDOMSelection
Filename = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
XDoc.Load (Filename)
Set point = XDoc.SelectNodes("/*")
Call ProcessChildNodes(point(0))
End Sub
Sub PrintNodeValue(Node As IXMLDOMNode)
If (Node.Attributes.Length = 0) Then
Row = Row + 1
mainWorkBook.Sheets("Sheet1").Cells(Row, 1).Value = Node.Text
End If
End Sub
Sub PrintAttributesValue(Node As IXMLDOMNode)
If (Node.Attributes.Length <> 0) Then
Row = Row + 1
For j = Node.Attributes.Length - 1 To 0 Step -1
strng = Node.Attributes.Length
mainWorkBook.Sheets("Sheet1").Cells(Row, strng - j).Value = Node.Attributes(j).Text
Next
End If
End Sub
Sub ProcessChildNodes(Node As IXMLDOMNode)
If (Node.HasChildNodes) Then
For m = 0 To Node.ChildNodes.Length - 1
If Node.ChildNodes(m).NodeType <> NODE_TEXT Then
Call PrintNodeValue(Node.ChildNodes(m))
Call PrintAttributesValue(Node.ChildNodes(m))
Call ProcessChildNodes(Node.ChildNodes(m))
End If
Next
Else
End If
End Sub