Import XML data into excel using VBA, selectnodes is not working (returns nothing)

Question

I have the below XML

<cqresponse xmlns="http://ibm.com/rational/cqweb/v7.1">
<rows>
    <row>
        <id>
            <![CDATA[ ABC00082474 ]]>
        </id>
        <Short_Desc>
            <![CDATA[
            Some Description 1
            ]]>
        </Short_Desc>
        <State>
            <![CDATA[ Some State 1 ]]>
        </State>
     </row>
     <row>
        <id>
            <![CDATA[ ABC00082475 ]]>
        </id>
        <Short_Desc>
            <![CDATA[
            Some Description 2
            ]]>
        </Short_Desc>
        <State>
            <![CDATA[ Some State 2 ]]>
        </State>
    </row>
</rows>
</cqresponse>

I want to import this into Excel using VB script. I had the below code, but the SelectNodes is not returning anything. It just returns Nothing

Sub Test()
    Dim nodeList As IXMLDOMNodeList
    Dim nodeRow As IXMLDOMNode
    Dim nodeCell As IXMLDOMNode
    Dim rowCount As Integer
    Dim cellCount As Integer
    Dim rowRange As Range
    Dim cellRange As Range
    Dim sheet As Worksheet
    Dim xpathToExtractRow As String
    Dim dom As DOMDocument60

    xpathToExtractRow = "/cqresponse/rows/row"

    Set dom = New DOMDocument60
    dom.Load ("C:\ABC.xml") ' this file contains the same xml data as mentioned above
    Set sheet = ActiveSheet
    Set nodeList = dom.SelectNodes(xpathToExtractRow)

    rowCount = 0
    For Each nodeRow In nodeList
        rowCount = rowCount + 1
        cellCount = 0
        For Each nodeCell In nodeRow.ChildNodes
            cellCount = cellCount + 1
            Set cellRange = sheet.Cells(rowCount, cellCount)
            cellRange.Value = nodeCell.Text
        Next nodeCell
    Next nodeRow
End Sub

Is there something wrong in this or do I need to take a different approach?

I meant VBA, dont know why it shows vb.net sometimes in the subject — Santosh
– Santosh, Commented Jun 24, 2016 at 16:08
it is because it was tagged as such. Somebody else edited your post to removed it. — litelite
– litelite, Commented Jun 26, 2016 at 13:40

Parfait · Accepted Answer · 2016-06-22 19:17:34Z

3

An often made mistake with XML parsing is the undeclared namespace in the root. As a result, you have to assign a namespace prefix during parsing of the document and use such a prefix in the XPath expression. Below, doc is assigned:

...
Dim xpathToExtractRow As String, XMLNamespaces As String
Dim dom As DOMDocument60

xpathToExtractRow = "/doc:cqresponse/doc:rows/doc:row"
XMLNamespaces = "xmlns:doc='http://ibm.com/rational/cqweb/v7.1'"

Set dom = New DOMDocument60
dom.Load ("C:\ABC.xml")
dom.setProperty "SelectionNamespaces", XMLNamespaces
...

answered Jun 22, 2016 at 19:17

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Santosh Over a year ago

Excellent! Thanks a lot for quick response. I could not test it immediately, but now I tested and it works well.

Collectives™ on Stack Overflow

Import XML data into excel using VBA, selectnodes is not working (returns nothing)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related