在具有VBA的XML文件中动态搜索数据 [英] Dynamically search for data In an XML file with VBA

问题描述

我需要在Excel中的VBA中打开一个XML文件。在XML文件中有两个字符串的数据，它们是一个标签。这些字符串可以在多个标签下找到，但是我也需要连接它们。

 <系统> 
< conveyor ConveyorNumber =132000> 
< conveyor> 132000< / conveyor> 
< productName> IQ< / productName> 
< / conveyor> 
< / Systems> 
  

有更多的数据，但我只需要

 < conveyor> 132000< / conveyor> 
  

&

 code><产品名称> IQ< / PRODUCTNAME> 
  

。有多个

 <系统>< /系统> 
  

在文件中，所以我需要计算文件中的系统连接所需的两个字符串并放置所有在Excel表中的列中。有没有这样做？

解决方案

尝试下面的代码解析XML文件

  Sub parseXML（）
 
 Dim strPath As String 
 strPath = Application.GetOpenFilename 
 
 Dim XDoc As Object 
设置XDoc = CreateObject（MSXML2.DOMDocument）
 XDoc.async = False 
 XDoc.validateOnParse = False 
 XDoc.Load（strPath）
设置xObjDetails = XDoc.ChildNodes（0）
设置xObject = xObjDetails.FirstChild 
对于xObjDetails.ChildNodes中的每个xObject 
 MsgBox子节点数& xObjDetails.ChildNodes.Length 
对于每个xChild在xObject.ChildNodes 
 MsgBox xChild.BaseName& & xChild.Text 
下一个xChild 
下一个xObject 
 
 End Sub 
  

I need to open up an XML file in the VBA in Excel. There are two strings of data I'm look for in the XML file that are under are a Tag. these strings can be found under multiple tags though, I also need concatenate them.

<Systems>
  <conveyor ConveyorNumber="132000">
    <conveyor>132000</conveyor>
    <productName>IQ</productName>
  </conveyor>
</Systems>

There's more data in there, but I only need

<conveyor>132000</conveyor>  

&

<productName>IQ</productName>

. There are multiple

<Systems></Systems>

in the file, so I need count the Systems in the file concatenate the two strings needed and place all of them in a column in an Excel sheet. Is there anyway to do this?

解决方案

Try below code to parse the XML file

Sub parseXML()

    Dim strPath As String
    strPath = Application.GetOpenFilename

    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False
    XDoc.validateOnParse = False
    XDoc.Load (strPath)
    Set xObjDetails = XDoc.ChildNodes(0)
    Set xObject = xObjDetails.FirstChild
    For Each xObject In xObjDetails.ChildNodes
        MsgBox "Child nodes count " & xObjDetails.ChildNodes.Length
        For Each xChild In xObject.ChildNodes
            MsgBox xChild.BaseName & " " & xChild.Text
        Next xChild
    Next xObject

End Sub

这篇关于在具有VBA的XML文件中动态搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！