CODEWITHSUNDEEP
xmlvbaexcel
CinCity
CinCity

Reputation: 149

Dynamically search for data In an XML file with VBA

I need to open up an XML file in the VBA in Excel. There are two strings of data I'm look for in the XML file that are under are a Tag. these strings can be found under multiple tags though, I also need concatenate them. 

<Systems>
  <conveyor ConveyorNumber="132000">
    <conveyor>132000</conveyor>
    <productName>IQ</productName>
  </conveyor>
</Systems>

There's more data in there, but I only need 

<conveyor>132000</conveyor>

&

<productName>IQ</productName>

. There are multiple 

<Systems></Systems>

in the file, so I need count the Systems in the file concatenate the two strings needed and place all of them in a column in an Excel sheet. Is there anyway to do this?

Upvotes: 4

Views: 6142

Answers (1)

Santosh
Santosh

Reputation: 12353

Try below code to parse the XML file 

Sub parseXML()

    Dim strPath As String
    strPath = Application.GetOpenFilename

    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False
    XDoc.validateOnParse = False
    XDoc.Load (strPath)
    Set xObjDetails = XDoc.ChildNodes(0)
    Set xObject = xObjDetails.FirstChild
    For Each xObject In xObjDetails.ChildNodes
        MsgBox "Child nodes count " & xObjDetails.ChildNodes.Length
        For Each xChild In xObject.ChildNodes
            MsgBox xChild.BaseName & " " & xChild.Text
        Next xChild
    Next xObject

End Sub

Upvotes: 5

Related Questions