如何在VBA中解析XML文件

问题描述：

我有以下格式的数据并且在Excel工作表中有类似的数据。如何在VBA中解析XML文件

<LegalEntityDataVO> 
    <LegalEntityDataVORow> 
     <Name>Siemens Corporation</Name> 
     <LegalEntityIdentifier>010</LegalEntityIdentifier> 
     <EstablishmentData> 
     <EstablishmentDataVORow> 
      <MainEstablishmentFlag>Y</MainEstablishmentFlag> 
      <Name>Siemens Corporation</Name> 
      <GeographyCode>US</GeographyCode> 
      <RegistrationDataEtb> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PROFILES</SourceTable> 
        <Name>United States Income Tax</Name> 
       </RegistrationDataEtbVORow> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PROFILES</SourceTable> 
        <Name>United States Federal Tax</Name> 
       </RegistrationDataEtbVORow> 
      </RegistrationDataEtb> 
     </EstablishmentDataVORow> 
     </EstablishmentData> 
     <EstablishmentData> 
     <EstablishmentDataVORow> 
      <MainEstablishmentFlag>Y</MainEstablishmentFlag> 
      <Name>US Corporation</Name> 
      <GeographyCode>US</GeographyCode> 
      <RegistrationDataEtb> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PAYBLES</SourceTable> 
        <Name>United States Service Tax</Name> 
       </RegistrationDataEtbVORow> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PAYBLES</SourceTable> 
        <Name>United States Oil Tax</Name> 
       </RegistrationDataEtbVORow> 
      </RegistrationDataEtb> 
     </EstablishmentDataVORow> 
     </EstablishmentData> 
    </LegalEntityDataVORow> 
<LegalEntityDataVO>

所以我的要求是比较Excel数据和XML数据。具体来说，我的任务描述如下：

If **LegalEntityIdentifier** value in Excel = **LegalEntityIdentifier** value in xml then 

( 
If(**MainEstablishmentFlag** value in Excel = **MainEstablishmentFlag** value in Xml then 

    (
     Compare **Name** in Excel with **Name** in XML 
    ) 
) 


**LegalEntityIdentifier** childnode of LegalEntityDataVORow 

**MainEstablishmentFlag** childnode of EstablishmentDataVORow 

**Name** childnode of RegistrationDataEtbVORow

这里是我所面临的问题：

每LegalEntityDataVORow包含许多EstablishmentDataVORow
每EstablishmentDataVORow包含许多RegistrationDataEtbVORow。

在我的XML文件中，我有100 <LegalEntityDataVORow>。我如何在VBA中运行上述任务？

http://*.com/questions/11305/how-to-parse-xml-using-vba – cullan

XML是一种树状结构的文档和Excel数据是单调的两维度格式。所以尽管内容可能，但这两者不能相似。请显示表格Excel数据。 – Parfait

答

将下面的代码生成的代码下面的输出输出以下文件：

代码：

Sub parse_data() 

    Dim strXmlFileName As String: strXmlFileName = "Drive:\Path\Filename.xml" 
    Dim docXmlDocument As New MSXML2.DOMDocument60 

    Dim wsDataToCompare As Worksheet: Set wsDataToCompare = ActiveWorkbook.Sheets("DataToCompare") 
    Dim strLegalEntityIdentifierToCompare As String: strLegalEntityIdentifierToCompare = wsDataToCompare.Cells(1, 1).Value 
    Dim strMainEstablishmentFlagToCompare As String: strMainEstablishmentFlagToCompare = wsDataToCompare.Cells(2, 1).Value 

    Dim ndeEntityData As IXMLDOMNode 
    Dim ndeEntityDataChild As IXMLDOMNode 
    Dim ndeEstablishmentData As IXMLDOMNode 

    Dim strNameToExtract As String 

    docXmlDocument.Load strXmlFileName 

    For Each ndeEntityData In docXmlDocument.DocumentElement.ChildNodes 

     If ndeEntityData.SelectSingleNode("LegalEntityIdentifier").Text = strLegalEntityIdentifierToCompare Then 

      For Each ndeEntityDataChild In ndeEntityData.ChildNodes 

       If ndeEntityDataChild.BaseName = "EstablishmentData" Then 

        If ndeEntityDataChild.SelectSingleNode("EstablishmentDataVORow/MainEstablishmentFlag").Text = strMainEstablishmentFlagToCompare Then 

         strNameToExtract = ndeEntityDataChild.SelectSingleNode("EstablishmentDataVORow/Name").Text 
         Debug.Print strNameToExtract 

        End If 

       End If 

      Next ndeEntityDataChild 

     End If 

    Next ndeEntityData 

End Sub

输出：

Siemens Corporation 
US Corporation

注意，我不得不扩大您的再次使XML文件有效。我使用的文件是：

<?xml version="1.0" encoding="UTF-8"?> 
<LegalEntityDataVO> 
    <LegalEntityDataVORow> 
     <Name>Siemens Corporation</Name> 
     <LegalEntityIdentifier>010</LegalEntityIdentifier> 
     <EstablishmentData> 
     <EstablishmentDataVORow> 
      <MainEstablishmentFlag>Y</MainEstablishmentFlag> 
      <Name>Siemens Corporation</Name> 
      <GeographyCode>US</GeographyCode> 
      <RegistrationDataEtb> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PROFILES</SourceTable> 
        <Name>United States Income Tax</Name> 
       </RegistrationDataEtbVORow> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PROFILES</SourceTable> 
        <Name>United States Federal Tax</Name> 
       </RegistrationDataEtbVORow> 
      </RegistrationDataEtb> 
     </EstablishmentDataVORow> 
     </EstablishmentData> 
     <EstablishmentData> 
     <EstablishmentDataVORow> 
      <MainEstablishmentFlag>Y</MainEstablishmentFlag> 
      <Name>US Corporation</Name> 
      <GeographyCode>US</GeographyCode> 
      <RegistrationDataEtb> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PAYBLES</SourceTable> 
        <Name>United States Service Tax</Name> 
       </RegistrationDataEtbVORow> 
       <RegistrationDataEtbVORow> 
        <SourceTable>XLE_ETB_PAYBLES</SourceTable> 
        <Name>United States Oil Tax</Name> 
       </RegistrationDataEtbVORow> 
      </RegistrationDataEtb> 
     </EstablishmentDataVORow> 
     </EstablishmentData> 
    </LegalEntityDataVORow> 
</LegalEntityDataVO>

如何在VBA中解析XML文件

相关推荐