Problems importing XML into Excel

[mpleam]

Hello

I'm trying to import an XML file into Excel, but all that happens is that only a single entry('Structure') is imported. I don't knpow if this is because the XML structure is invalid or something - I know nothing about XML, so so I don't know.

Here is a sample of the file:

  <!-- LBC peripheral faults -->
  <Fault>
    <Name>LBC peripheral reported unsupported header</Name>
    <FaultID>{6dcabdc3-a5ac-4a51-b988-dc2b20858e71}</FaultID>
    <Actions>
      <LogEvent />
      <UpdateFaultOutput>
        <OnActivate>enable</OnActivate>
      </UpdateFaultOutput>
    </Actions>
    <TranslationComment>LBC is a proprietary protocol, don't translate that part. Header as in header/footer of a document.</TranslationComment>
  </Fault>
  <Fault>
    <Name>LBC peripheral reported checksum invalid</Name>
    <FaultID>{bef72d2c-243d-4502-a55b-515906f6fbc1}</FaultID>
    <Actions>
      <LogEvent />
      <UpdateFaultOutput>
        <OnActivate>enable</OnActivate>
      </UpdateFaultOutput>
    </Actions>
    <TranslationComment>LBC is a proprietary protocol, don't translate that part.</TranslationComment>
  </Fault>
  <Fault>
    <Name>LBC peripheral reported unexpected sequence number</Name>
    <FaultID>{1ba343ae-58ed-4bff-b192-c90a66711ace}</FaultID>
    <Actions>
      <LogEvent />
      <UpdateFaultOutput>
        <OnActivate>enable</OnActivate>
      </UpdateFaultOutput>
    </Actions>
    <TranslationComment>LBC is a proprietary protocol, don't translate that part.</TranslationComment>
  </Fault>

What I am trying to do is take this XML and bring just certain data into an Excel worksheet - the fault header comment, the fault name, fault ID, and the comment (see the attached screenshot). Is it possible to use VBA to do this? The file is way too big to do all this manually.

I am open to any suggestions, and all help will be most gratefully received!

Mark

 

[mpleam]

OK - sorted!

I found this code on StackOverflow:

Public Sub XML_To_xlsx()
    Dim FilePath As String
    Dim Book As Workbook

'   Load XML Data to New Workbook
    FilePath = "C:\temp\test.xml"
    Set Book = Workbooks.OpenXML(FilePath)

   'Copy to active Worksheet
    Book.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")

    'Close New Workbook
    Book.Close False
End Sub

And it imported the xml fine. Had to delete the unneeded content, but good enough!