vba 如何在excel中打开一个XML文件

How to open a XML file in excel

提问by Samy

I want program my button with this features:

我想用以下功能对我的按钮进行编程：

open and select a xml file then write in "table2" the metric name id M1-M10 with the belonging values.

打开并选择一个 xml 文件，然后在“table2”中写入指标名称 id M1-M10 以及所属值。

I was successful to open and select a xml file:

我成功打开并选择了一个xml文件：

Private Sub btn_load_xml_Click()
'-----------Open file---------------------------
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant

' File filters
Filter = "XML Files (*.xml),*.xml"
' Default Filter to *.*

FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"

' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:")
With Application
    ' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With

' Exit on Cancel
If Filename = False Then
    Exit Sub
End If

' Open File   
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
'------------- Load XML in table2------------------

'------------- END Load XML in table2------------------
End Sub

But how can I load the XML file and work with it ?

但是如何加载 XML 文件并使用它呢？

Here is my XML structure

这是我的 XML 结构

<?xml version="1.0" encoding="UTF-8" ?>

<sourcemonitor_metrics>
  <project version="3.3">
    <project_name>metric_auswertung</project_name>
    <project_directory>C:\Users\SULAS\Desktop\Metric_auswertung</project_directory>
    <project_language>C</project_language>

    <ignore_headers_footers>false</ignore_headers_footers>
    <export_raw_numbers>false</export_raw_numbers>
    <metric_names name_count="13">
      <metric_name id="M0" type="number">Lines</metric_name>
      <metric_name id="M1" type="number">Statements</metric_name>
      <metric_name id="M2" type="percent" divisor="M1">Percent Branch Statements</metric_name>
      <metric_name id="M3" type="percent" divisor="M0">Percent Lines with Comments</metric_name>
      <metric_name id="M4" type="number">Functions</metric_name>
      <metric_name id="M5" type="average" divisor="M4">Average Statements per Function</metric_name>
      <metric_name id="M6" type="string">Line Number of Most Complex Function</metric_name>
      <metric_name id="M7" type="string">Name of Most Complex Function</metric_name>
      <metric_name id="M8" type="maximum">Complexity of Most Complex Function</metric_name>
      <metric_name id="M9" type="string">Line Number of Deepest Block</metric_name>
      <metric_name id="M10" type="maximum">Maximum Block Depth</metric_name>
      <metric_name id="M11" type="average" values="block_depths">Average Block Depth</metric_name>
      <metric_name id="M12" type="average">Average Complexity</metric_name>
    </metric_names>
    <checkpoints checkpoint_count="1">
      <checkpoint checkpoint_name="Metric_Auswertung" ignore_blank_lines="false" modified_complexity="true" checkpoint_date="2013-02-25">
        <files file_count="3">
          <file file_name="Mcu - Kopie.c">
            <metrics metric_count="13">
              <metric id="M0">603</metric>
              <metric id="M1">183</metric>
              <metric id="M2">26,2</metric>
              <metric id="M3">23,2</metric>
              <metric id="M4">11</metric>
              <metric id="M5">24,6</metric>
              <metric id="M6">321</metric>
              <metric id="M7">Mcu_GetPllStatus()</metric>
              <metric id="M8">15</metric>
              <metric id="M9">235</metric>
              <metric id="M10">6</metric>
              <metric id="M11">1,97</metric>
              <metric id="M12">6,00</metric>
            </metrics>
          </file>
        </files>
      </checkpoint>
    </checkpoints>
  </project>
</sourcemonitor_metrics>

回答by Joshua Honig

You can work directly with XML documents using MSXML. For complete reference see MSXML, and check out the DOM referencein particular. To use MSXML most easily, open the VBA editor and go to Tools > References. Add a check next to "Microsoft XML, vX.0" where X is the latest version available.

您可以使用 MSXML 直接处理 XML 文档。有关完整参考，请参阅MSXML，并特别查看DOM 参考。要最轻松地使用 MSXML，请打开 VBA 编辑器并转到“工具”>“参考”。在“Microsoft XML, vX.0”旁边添加一个复选框，其中 X 是可用的最新版本。

"How to use XML" is beyond the scope of this question, but here's the basic VBA to get you started:

“如何使用 XML”超出了这个问题的范围，但这里是帮助您入门的基本 VBA：

Sub btn_load_xml_Click()

    ' Get file name ...

    Dim oDoc As New MSXML2.DOMDocument60
    Dim xMetricNames As IXMLDOMNodeList
    Dim xMetricName As IXMLDOMElement
    Dim xMetrics As IXMLDOMNode
    Dim xMetric As IXMLDOMElement
    Dim mtID As String, mtName As String, mtValue As String

    ' Load from file
    oDoc.Load FileName

    ' Select needed nodes
    Set xMetrics = oDoc.SelectSingleNode("//project/checkpoints/checkpoint/files/file/metrics")
    Set xMetricNames = oDoc.SelectNodes("//project/metric_names/metric_name")
    For Each xMetricName In xMetricNames
        mtName = xMetricName.Text
        mtID = xMetricName.getAttribute("id")
        mtValue = xMetrics.SelectSingleNode("metric[@id='" & mtID & "']").Text

        ' Do whatever you want with these values
    Next

    Set oDoc = Nothing

End Sub

回答by Ripster

Have you tried:

你有没有尝试过：

Workbooks.OpenXML Filename:="C:\Path\File.xml", LoadOption:=xlXmlLoadImportToList

This will open the XML file as an XML Table in excel. You can then work on it like any other excel document.

这将在 Excel 中将 XML 文件作为 XML 表打开。然后，您可以像处理任何其他 Excel 文档一样处理它。