How to open a XML file in excel

Question

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.

I was successful to open and select a xml file:

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 ?

Here is my XML structure

<?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>

Solution 1

You can work directly with XML documents using MSXML. For complete reference see MSXML, and check out the DOM reference in 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.

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

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

Solution 2

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.

Comments

[Samy]

But I want not the whole XML just some parts. Everthing should be happen in the active table not in a new table

[Ripster]

Once the file is open can you not just copy the data from the new table to your active table then close the xml file?

[Samy]

I want do it with one click, it should automatically take the important data. This app is used many times so its better do it with one click

[Ripster]

Still one click, I mean have your code pull the data needed and then close the workbook.

[barrowc]

MSXML2.DOMDocument is always a synonym for MSXML2.DOMDocument30 from MSXML, v3.0. Later versions need to use the specific ProgIDs - e.g. DOMDocument60 for MSXML, v6.0 - see here

[Samy]

How can I get the value "Mcu - Kopie.c" in <file file_name="Mcu - Kopie.c"> ?

[Joshua Honig]

@Samy Dim xFile : Set xFile = oDoc.SelectSingleNode("//project/checkpoints/checkpoint/file‌​s/file") : xFile.getAttribute("file_name")

[Samy]

I have in //project/checkpoints/checkpoint/files/ more then one file and I want just get the values(603,18326,2...) from the file with this attribute: file_name="Mcu - Kopie.c" .....sry hope you can understand it

[Joshua Honig]

@Samy Ah, I see. I think this is what you want. Set xMetrics = oDoc.SelectSingleNode("//project/checkpoints/checkpoint/file‌​s/file[@file_name='M‌​cu - Kopie.c']/metrics") For more info, check out XPath Reference.