使用vba excel解析xml文件，获取excel工作表的特定值。
使用vba excel解析xml文件，获取excel工作表的特定值。
提问于 2022-02-17 11:16:54
你好，我是编程新手，我正在学习它。我不理解xml和VBA，但是这个任务已经分配给我了。我试着在网上到处搜索，想找到一个解决方案，但由于我的知识不多，我想不出答案。
我的问题是，我需要从文件excel中的xml文件中提取特定的值。
<NODE >
<ANOTHER-NODE>
<XXX name1="value" name2="value2" name3="value3"/>
<XXX name1="value" name2="value2" name3="value3"/>
<XXX name1="value" name2="value2" name3="value3"/>
<XXX name1="value" name2="value2" name3="value3"/>
<XXX name1="value" name2="value2" name3="value3"/>
<XXX name1="value" name2="value2" name3="value3"/>
<ANOTHER-NODE2>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
<File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
这就像我所拥有的结构，我需要在excel中提取来自all的“name1=”值和来自所有的N="xyxyxyxy“。
Private Sub CommandButtonImport_Click()
Dim xmlr As Office.FileDialog
Set xmlr = Application.FileDialog(msoFileDialogFilePicker)
With xmlr
.Filters.Clear
.Title = "Seleziona un File XML"
.Filters.Add "XML File", "*.xml", 1
.AllowMultiSelect = False
If .Show = True Then
XmlFileName = .SelectedItems(1)
Dim xmlDoc As MSXML2.DOMDocument60
Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
If xmlDoc.Load(XmlFileName) = True Then这是我创建的结构，用于携带xml文件，但我不知道它是否正确。
这是我在这里的第一篇文章，希望这是可以理解的。
更新
非常感谢您的回答，不好意思，对于xml文件，我是自己做的，因为原始的数据是我无法共享的，最后我发现您的提示很有帮助，我编写了完成这项工作的代码，我分享了它们。
Private Sub CommandButtonImport_Click()
Dim xmlr As Office.FileDialog
Set xmlr = Application.FileDialog(msoFileDialogFilePicker)
With xmlr
.Filters.Clear
.Title = "Seleziona un File XML"
.Filters.Add "XML File", "*.xml", 1
.AllowMultiSelect = False
If .Show = True Then
XmlFileName = .SelectedItems(1)
Dim xmlDoc As MSXML2.DOMDocument60
Dim ECU As MSXML2.IXMLDOMNodeList
Dim File As MSXML2.IXMLDOMNodeList
Dim Feature As MSXML2.IXMLDOMNodeList
Dim NodoLista As MSXML2.IXMLDOMNode
Dim NodoLista1 As MSXML2.IXMLDOMNode
Dim NodoLista2 As MSXML2.IXMLDOMNode
Dim i As Integer
Dim k As Integer
Dim l As Integer
Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
If xmlDoc.Load(XmlFileName) = True Then
Set ECU = xmlDoc.SelectNodes("//XXX")
On Error Resume Next
For Each NodoLista In ECU
i = i + 1
With ThisWorkbook.Sheets("Foglio1").Rows(i)
.Cells(1).Value = NodoLista.Attributes(0).NodeValue
End With
Next NodoLista
Set File = xmlDoc.SelectNodes("//File")
On Error Resume Next
For Each NodoLista1 In File
k = k + 1
With ThisWorkbook.Sheets("Foglio1").Rows(k)
.Cells(3).Value = NodoLista1.Attributes(0).NodeValue
End With
Next NodoLista1
Set Feature = xmlDoc.SelectNodes("//Feature")
On Error Resume Next
For Each NodoLista2 In Feature
l = l + 1
With ThisWorkbook.Sheets("Foglio1").Rows(l)
.Cells(5).Value = NodoLista2.Attributes(0).NodeValue
End With
Next NodoLista2
End If
End If
End With结束子对象
如果我可以问另一件事，我想更新这段代码，在xml上执行另一个控件，我有属性值出现的次数超过一次，有一个命令行不要在excel中打印相同的属性，而不是一次？
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-02-17 19:00:42
到目前为止，您尝试过的内容有一些不一致之处(没有尝试自己的一些代码来获得xml节点或属性值)；我将回答这些问题，只因为以下几点可能有助于克服常见的启动问题，然后再检查并重新尝试您自己的代码(c.f )。@TimWilliams (上文评论)：
- xml结构不是格式良好的，因为缺少
<ANOTHER-NODE>和<ANOTHER-NODE2>的结束标记，因此加载将失败；这就是为什么我插入了一个TESTONLY变量，以便将格式良好的修改后的xml结构加载为xml字符串(通过LoadXML而不是外部文件引用)--您可以轻松地将该常量更改为TESTONLY=FALSE，以便通过引用其文件名的Load开始加载实际文件。H 210F 211
似乎您希望使用XML对象的intelliSense).后期绑定，尽管在这里使用了文档声明类型Dim xmlDoc As MSXML2.DOMDocument60，它需要对"Microsoft，v6.0“(所谓的早期绑定)的显式库引用；但是，在这种方法中，我仅通过Dim ... As Object演示对象的后期绑定和声明(警告:不使用Dim ... As Object)。
此示例代码假设
<NODE>，即所谓的documentElement (~ "root“节点)，在任何其他情况下，它都需要修改XPath和/或代码。因此，在获得具有从属节点的节点列表之前，我指的是DocumentElement。这是通过一个所谓的XPath表达式来执行的，它使用XMLDOM方法、和N作为子节点<ANOTHER-NODE>和的第一个属性，每个遵循兄弟关系(此处仅为：<ANOTHER-NODE2>)。请注意，curNode.Attributes.Item(0).
这样的基于零的索引在XMLDOM中引用属性。
XMLDOM (Document )是一个跨平台和与语言无关的接口，将文档视为树结构，并允许使用自己的方法和属性对树进行编程访问。但是，您可以使用XPath表达式的特殊语法，例如“/NODE/另一个节点/@name1”(甚至是在本文中假定的文档元素上应用的*/*/@name1 )来解决分层xml文档结构中的任何逻辑部分。
示例代码
Private Sub CommandButtonImport_Click()
'...FileDialog etc.
Dim XmlFileName
'... further stuff ' << insert your FileDialog code
'...
Dim xmlDoc As Object ' << Late binding XML
Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
Const TESTONLY As Boolean = True ' << Change to your needs!
If TESTONLY Then ' << Only for testing xml string
'Define wellformed xml content string(!) to make it reproducable
Dim wellformed As String
wellformed = getTestContent() ' << calls help function
If Not xmlDoc.LoadXML(wellformed) Then ' LoadXML; Escape if Load error!
Debug.Print "Cannot load!": Exit Sub
End If
Else ' Load a file identified by FileName as in OP
If Not xmlDoc.Load(XmlFileName) Then ' Load; Escape if Load rror!
Exit Sub
End If
End If
'start writing to VB Editor's immediate window
Debug.Print "Main Node", "Subnode", "Attrib[1]", "Value" & vbNewLine & String(70, "-")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Define & apply XPath expression
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim XPth As String
XPth = "*/*"
Dim SubNodes As Object
Set SubNodes = xmlDoc.DocumentElement.SelectNodes(XPth)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Loop through each Node in the referenced subnodes
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim curNode As Object
For Each curNode In SubNodes
Debug.Print _
curNode.SelectSingleNode("..").nodename, _
curNode.nodename, _
curNode.Attributes.Item(0).Name, _
curNode.Attributes.Item(0).Text
Next curNode
End Sub帮助函数` `getTestContent()
Function getTestContent()
getTestContent = _
"<NODE>" & _
"<ANOTHER-NODE>" & _
"<XXX name1='valueA' name2='value2' name3='value3'/>" & _
"<XXX name1='valueB' name2='value2' name3='value3'/>" & _
"<XXX name1='valueC' name2='value2' name3='value3'/>" & _
"</ANOTHER-NODE>" & _
"<ANOTHER-NODE2>" & _
"<File N='xyxyxyxy1' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
"<File N='xyxyxyxy2' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
"<File N='xyxyxyxy3' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
"<File N='xyxyxyxy4' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
"</ANOTHER-NODE2>" & _
"</NODE>"
End Function示例TESTONLY在VB编辑器立即窗口中的输出
Main Node Subnode Attrib[1] Value
----------------------------------------------------------------------
ANOTHER-NODE XXX name1 valueA
ANOTHER-NODE XXX name1 valueB
ANOTHER-NODE XXX name1 valueC
ANOTHER-NODE2 File N xyxyxyxy1
ANOTHER-NODE2 File N xyxyxyxy2
ANOTHER-NODE2 File N xyxyxyxy3
ANOTHER-NODE2 File N xyxyxyxy4页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71157125
复制相关文章
相似问题
腾讯云开发者
