使用 VBA 和 xPath 遍历 XML 文件

Loop through XML File using VBA and xPath

提问by BBQ Chef

I'm stuck with parsing/extracting my XML with xPath in my MS Project VBA code.

我坚持在我的 MS Project VBA 代码中使用 xPath 解析/提取我的 XML。

Why can't I select a node in this node?

为什么我不能在这个节点中选择一个节点？

Set nodes = xml.SelectNodes("/config/ProjectFile")
For Each node In nodes
    With Me.lbProjList
      '.AddItem (xmlText(node.SelectSingleNode("/FileName")))
      '.Column(1, i) = xmlText(node.SelectSingleNode("/LastSaveDate"))
    End With
    i = i + 1
    Debug.Print i & " file " & node.xml ' Shows the XML I expected
    Debug.Print "  Name: " & node.SelectSingleNode("/FileName").Text ' Doesn't work! Error 91
Next node

I'd be glad to get some help!!!

我很乐意得到一些帮助！！！

Thanks!

谢谢！

---

---

Here is the complete VBA code:

这是完整的 VBA 代码：

Private Sub ProjListFill()
  Dim i As Integer
  Dim xml As MSXML2.DOMDocument60
  Dim nodes As MSXML2.IXMLDOMNodeList 'CustomXMLNodes???
  Dim node As MSXML2.IXMLDOMNode 'CustomXMLNode???
  Dim n As CustomXMLPart 'CustomXMLNode???

  ' clear form before fill it
  Me.lbProjList.Clear
  Me.txtHeadline.value = ""
  Me.txtUpdateURL.value = ""
  Me.txtBoxParam.value = ""
  Me.txtBoxPrefix.value = ""

  Set xml = readXML(CustomProperty("XMTMLMonitoring_AppPath") & "\" & m2w_config("SubFolder") & "\" & m2w_config("SubFolderData") & "\" & m2w_config("XMLConfigFileName"))

  i = 0
  Set nodes = xml.SelectNodes("/config/ProjectFile")
  For Each node In nodes
    With Me.lbProjList
      '.AddItem (xmlText(node.SelectSingleNode("/FileName")))
      '.Column(1, i) = xmlText(node.SelectSingleNode("/LastSaveDate"))
    End With
    i = i + 1
    Debug.Print i & " file " & node.xml ' Shows the XML I expected        Debug.Print "  Name: " & node.SelectSingleNode("/FileName").Text ' Doesn't work! Error 91
  Next node

  Debug.Print i & " Project files found in config.xml"

  ' fill text boxes
  Me.txtHeadline.value = xmlText(xml.SelectSingleNode("/config/Custom/Headline"))
  Me.txtUpdateURL.value = xmlText(xml.SelectSingleNode("/config/Custom/UpdateURL"))
  Me.txtBoxParam.value = xmlText(xml.SelectSingleNode("/config/Custom/BoxParam"))
  Me.txtBoxPrefix.value = xmlText(xml.SelectSingleNode("/config/Custom/BoxPrefix"))

ExitProjListFill:
  Exit Sub
End Sub

Here is the XML

这是 XML

<config id="config" ConfigSaveDate="2011-03-31 21:32:55" ConfigSchemaVersion="1.02">
    <Custom> 
        <DateFormat>yyyy-mm-dd hh:mm:ss</DateFormat>
        <Headline>Project Overview</Headline>
        <UpdateHref></UpdateHref>
        <BoxParam>ModelYear</BoxParam><BoxPrefix>MY </BoxPrefix>
    </Custom>
    <Program>
        <DateFormat>yyyy-mm-dd hh:mm:ss</DateFormat> 
    </Program>
    <ProjectFile ProjectFileName="projectfile1.mpp">
        <RevisionNumber>201</RevisionNumber> 
        <FileName>projectfile1.mpp</FileName> 
        <LastSaveDate>2011-03-23 16:45:19</LastSaveDate> 
    </ProjectFile>
    <ProjectFile ProjectFileName="projectfile2bedeleted.mpp">
        <RevisionNumber>115</RevisionNumber> 
        <FileName>projectfile2b.mpp</FileName> 
        <LastSaveDate>2011-03-31 21:12:55</LastSaveDate> 
    </ProjectFile>
    <ProjectFile ProjectFileName="projectfile2.mpp">
        <RevisionNumber>315</RevisionNumber> 
        <FileName>projectfile3.mpp</FileName> 
        <LastSaveDate>2011-03-31 21:32:55</LastSaveDate> 
    </ProjectFile>
</config>

回答by Jean-Fran?ois Corbett

Instead of using the absolute path,

而不是使用绝对路径，

node.SelectSingleNode("/FileName").Text

try using the relative path (without the /):

尝试使用相对路径（没有/）：

node.SelectSingleNode("FileName").Text

Disclaimer: Since you haven't shown us a sample of your xml file, this is largely conjecture...

免责声明：由于您尚未向我们展示您的 xml 文件示例，这在很大程度上是推测...

回答by Anant Gupta

I had to do some parsing of my own, and after a few hours, I realized that it can get difficult to get to your data with a single XPath. There can be numerous reasons:

我不得不自己进行一些解析，几个小时后，我意识到使用单个 XPath 获取数据会变得很困难。可能有很多原因：

1. Bad XML Structure
2. Repetitive tags
3. Etc, etc

1. 错误的 XML 结构
2. 重复标签
3. 等等等等

I used a combination of XPath and builtin functions to get to my data. e.g.

我结合使用 XPath 和内置函数来获取我的数据。例如

strFile = "C:\MyFile.xml"
intFile = 2
Open strFile For Input As intFile

'Load XML into string strXML
While Not EOF(intFile)
    Line Input #intFile, strXML
Wend
Close intFile

Dim XMLDOC As MSXML2.DOMDocument
Set objDOM = CreateObject("Msxml2.DOMDocument.6.0")
Dim xmlNodes As MSXML2.IXMLDOMNodeList
objDOM.LoadXML strXML
XPath = "/query/results"
Set xmlNode = objDOM.SelectNodes(XPath)
rowCounter = WorksheetFunction.CountA(output.Columns("A")) + 1
Set oNodes = objDOM.getElementsByTagName("quote")
If oNodes.Length > 0 Then
    For Each oNode In oNodes
        output.Cells(rowCounter, 1) = symbol
        output.Cells(rowCounter, 2) = oNode.SelectSingleNode("Date").Text
        output.Cells(rowCounter, 3) = oNode.SelectSingleNode("Open").Text
        output.Cells(rowCounter, 4) = oNode.SelectSingleNode("High").Text
        output.Cells(rowCounter, 5) = oNode.SelectSingleNode("Low").Text
        output.Cells(rowCounter, 6) = oNode.SelectSingleNode("Close").Text
        output.Cells(rowCounter, 7) = oNode.SelectSingleNode("Volume").Text
        output.Cells(rowCounter, 8) = oNode.SelectSingleNode("Adj_Close").Text
        rowCounter = rowCounter + 1
    Next oNode
End If

You can find the Excel sheet present at my blog.

您可以在我的博客中找到 Excel 表 。