gpt4 book ai didi

xml - 如何使用 Excel VBA 导入 XML 数据?

转载 作者:行者123 更新时间:2023-12-04 21:16:18 25 4
gpt4 key购买 nike

我正在尝试从这样的 XML 文件中导入数据:

<library>
<book>
<title>aaa</title>
<author>aaa-author</author>
</book>
<book>
<title>bbb</title>
<author>bbb-author</author>
</book>
<book>
<title>ccc</title>
</book>
</library>

(注意第三本书对作者没有值(value))

我想获得一个 Excel 表格,其中每本书的数据都显示在一行上。问题是我不明白我必须如何在书节点上循环以获得它们的子值。

我正在处理这样的代码:
Set mainWorkBook = ActiveWorkbook
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "C:\example.xml"
oXMLFile.Load (XMLFileName)
Set Books = oXMLFile.SelectNodes("/book")
For i = 0 To (Books.Length - 1)
' I cannot understand this part
Next

最佳答案

添加引用( 工具 -> 引用 ... )到 微软 XML 6.0 .这将允许您输入变量( Dim book As IXMLDOMNode ),这将为您提供 Intellisense。

然后您可以使用以下代码,它遍历所有 book元素,保存 titleauthor到二维数组中(如果它们可用),然后将数组粘贴到 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

'Load XML from the file
oXMLFile.Load "C:\example.xml"

'Get a list of book elements
Set books = oXMLFile.SelectNodes("/library/book")
booksUBound = books.Length - 1

'Create a two-dimensional array to hold the results
ReDim results(booksUBound, 1)

'Iterate through all the book elements, putting the title and author into the array, when available
For i = 0 To booksUBound
Set book = books(i) 'A For Each loop would do this automatically, but we need the
'index to put the values in the right place in the array
Set title = book.SelectSingleNode("title")
If Not title Is Nothing Then results(i, 0) = title.Text
Set author = book.SelectSingleNode("author")
If Not author Is Nothing Then results(i, 1) = author.Text
Next

'Paste the results into the worksheet
Dim wks As Worksheet
Set wks = ActiveSheet
wks.Range(wks.Cells(1, 1), wks.Cells(books.Length, 2)) = results

链接:
  • A Beginner's Guide to the XML DOM
  • Query XML Files Using VBScript
  • Progarm the DOM with Visual Basic - 包含许多不错的演练

  • 引用:
  • XML DOM - Concepts , Reference
  • selectNodes
  • selectSingleNode
  • VBA - Dim statement
  • Excel - Range property , Cells property
  • 关于xml - 如何使用 Excel VBA 导入 XML 数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32329194/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com