使用Excel VBA导入XML数据

Question

我正在尝试从XML文件导入特定数据到Excel工作表。

我使用的代码是这样的。

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

使用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>

我的问题是我的XML文件看起来像这样。

<?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>

我原本以为只要把

替换掉就可以了

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

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")

但是这会给我一个运行时错误。

如果有人知道我对原始代码做了什么改变，我将不胜感激。

Answer 1

这对我有用:

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

使用这个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>