CODEWITHSUNDEEP
excelxmlvbamsxml
FredrikS
FredrikS

Reputation: 15

Import XML data using Excel VBA

I'm trying to import specific data from and XML file to an Excel sheet.

The code I'm using is this.

Dim oXMLFile As New DOMDocument60
Dim books As IXMLDOMNodeList
Dim results() As String
Dim i As Integer, booksUBound As Integer
Dim book As IXMLDOMNode, title As IXMLDOMNode, author As IXMLDOMNode

oXMLFile.Load "C:\example.xml"

Set books = oXMLFile.SelectNodes("/OUT_MESSAGE/LINES/OUT_MESSAGE_LINE")
booksUBound = books.Length - 1

ReDim results(booksUBound, 1)

For i = 0 To booksUBound
    Set book = books(i) 
    Set title = book.SelectSingleNode("C00")
    If Not title Is Nothing Then results(i, 0) = title.Text
Next

Dim wks As Worksheet
Set wks = ActiveSheet
wks.Range(wks.Cells(1, 1), wks.Cells(books.Length, 2)) = results

Which works with this XML

<?xml version="1.0" encoding="UTF-8"?>
<OUT_MESSAGE>
 <LINES>
  <OUT_MESSAGE_LINE>
   <C00>1231231</C00>
   <C01>3213213</C01>
  </OUT_MESSAGE_LINE>
  <OUT_MESSAGE_LINE>
   <C00>1231234</C00>
   <C01>3213214</C01>
  </OUT_MESSAGE_LINE>
 </LINES>
</OUT_MESSAGE>

My problem is that my XML file looks like this.

<?xml version="1.0" encoding="UTF-8"?>
<OUT_MESSAGE xmlns="urn:randomaddress-com:schema:test_out_message" xmlns:xsi="http://www.randomurl.com/123">
 <LINES>
  <OUT_MESSAGE_LINE>
   <C00>1231231</C00>
   <C01>3213213</C01>
  </OUT_MESSAGE_LINE>
  <OUT_MESSAGE_LINE>
   <C00>1231234</C00>
   <C01>3213214</C01>
  </OUT_MESSAGE_LINE>
 </LINES>
</OUT_MESSAGE>

Which I originally thought I could simply get to work by replacing

Set books = oXMLFile.SelectNodes("/OUT_MESSAGE/LINES/OUT_MESSAGE_LINE")

With

Set books = oXMLFile.SelectNodes("/OUT_MESSAGE xmlns='urn:randomaddress-com:schema:test_out_message' xmlns:xsi='http://www.randomurl.com/123'/LINES/OUT_MESSAGE_LINE")

But that gives me a runtime error.

If anyone know what changes I have to do to the original code that would be much appreciated.

Upvotes: 1

Views: 500

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

This worked for me:

Dim xDoc, nodes, oNode

Set xDoc = CreateObject("MSXML2.DOMDocument.6.0")

'Note: added an `x=` to the default namespace so we can reference it later
xDoc.setProperty "SelectionNamespaces", _
                 "xmlns:x='urn.randomaddress.com.schema.test_out_message'"

xDoc.LoadXML Sheet2.Range("A4").Value 'load XML from sheet

'use the "x" prefix we added above
Set nodes = xDoc.SelectNodes("/x:OUT_MESSAGE/x:LINES/x:OUT_MESSAGE_LINE")
Debug.Print nodes.Length  '  = 1

For Each oNode In nodes
    Debug.Print oNode.SelectSingleNode("x:C00").nodeTypedValue
    Debug.Print oNode.SelectSingleNode("x:OBJSTATE").nodeTypedValue
    'etc
Next oNode

using this XML:

<?xml version="1.0"?>
<OUT_MESSAGE xmlns="urn.randomaddress.com.schema.test_out_message" 
xmlns:xsi="http://www.randomurl.com/123">
 <LINES>
  <OUT_MESSAGE_LINE>
   <C00>321312</C00>
   <C01>12312312</C01>
   <OBJSTATE>Posted</OBJSTATE>
   <OBJEVENTS>Accept^Reject^</OBJEVENTS>
   <STATE>Posted</STATE>
  </OUT_MESSAGE_LINE>
 </LINES>
</OUT_MESSAGE>

Upvotes: 1

Related Questions