I am trying to extract 2 bits of information from the following xml file using VBA but they seem really buried and can't seem to access them, I am very new to xml so this is a bit beyond me. I have found a number of examples on the web that I can make work using a simple xml file, but not this.
On the xml below I have tagged the two bits of information I need to pull out into a spreadsheet - the first one I need will form the header to a column, and then the second piece might occour a number of times below.
Sub ReadXML()
Call fnReadXMLByTags
End Sub
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/instrument/member/list/obj/member/string")
End Function
<?xml version="1.0"?>
<instrument>
<string name="name" value="TEST" wide="true"/> <!--I NEED TO EXTRACT THIS FIRST-->
<member name="slotvisuals">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="USlotVisuals" ID="882527840">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="CUSTOM ART1" wide="true"/>
<string name="description" value="CUSTOM ART1 DESCRIPTION" wide="true"/>
<int name="group" value="0"/>
</obj>
<obj class="USlotVisuals" ID="45186017184">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 2" wide="true"/>
<string name="description" value="GROUP 2" wide="true"/>
<int name="group" value="1"/>
</obj>
<obj class="USlotVisuals" ID="882712304">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 3" wide="true"/>
<string name="description" value="GROUP 3" wide="true"/>
<int name="group" value="2"/>
</obj>
<obj class="USlotVisuals" ID="44402087248">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 4" wide="true"/>
<string name="description" value="GROUP 4" wide="true"/>
<int name="group" value="3"/>
</obj>
</list>
</member>
<member name="slots">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="PSoundSlot" ID="2271687808">
<obj class="PSlotThruTrigger" name="remote" ID="2276282784">
<int name="status" value="144"/>
<int name="data1" value="0"/>
</obj>
<obj class="PSlotMidiAction" name="action" ID="2268706176">
<int name="version" value="600"/>
<member name="noteChanger">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="PSlotNoteChanger" ID="2277009712">
<int name="channel" value="-1"/>
<float name="velocityFact" value="1"/>
<float name="lengthFact" value="1"/>
<int name="minVelocity" value="0"/>
<int name="maxVelocity" value="127"/>
<int name="transpose" value="0"/>
<int name="minPitch" value="0"/>
<int name="maxPitch" value="127"/>
</obj>
</list>
</member>
<member name="midiMessages">
<int name="ownership" value="1"/>
</member>
<int name="channel" value="-1"/>
<float name="velocityFact" value="1"/>
<float name="lengthFact" value="1"/>
<int name="minVelocity" value="0"/>
<int name="maxVelocity" value="127"/>
<int name="transpose" value="0"/>
<int name="maxPitch" value="127"/>
<int name="minPitch" value="0"/>
<int name="key" value="-1"/>
</obj>
<member name="sv">
<int name="ownership" value="2"/>
<list name="obj" type="obj">
<obj class="USlotVisuals" ID="909444640">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="CUSTOM ART1" wide="true"/>
<string name="description" value="CUSTOM ART1 DESCRIPTION" wide="true"/>
<int name="group" value="0"/>
</obj>
<obj class="USlotVisuals" ID="43544745280">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 2" wide="true"/>
<string name="description" value="GROUP 2" wide="true"/>
<int name="group" value="1"/>
</obj>
<obj class="USlotVisuals" ID="44505641184">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 3" wide="true"/>
<string name="description" value="GROUP 3" wide="true"/>
<int name="group" value="2"/>
</obj>
<obj class="USlotVisuals" ID="44402092688">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 4" wide="true"/>
<string name="description" value="GROUP 4" wide="true"/>
<int name="group" value="3"/>
</obj>
</list>
</member>
<member name="name">
<string name="s" value="THIS IS SLOT 1" wide="true"/> <!--AND THEN THIS-->
</member>
<int name="color" value="1"/>
</obj>
</list>
</member>
<member name="controller">
<int name="ownership" value="1"/>
</member>
</instrument>
CodePudding user response：
This should get what you are looking for in the query you already have (which worked for me). You should be able to use similar code to get the header, depending on how the query should go.
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/instrument/member/list/obj/member/string")
r = 2
With mainWorkBook.Sheets("Sheet1")
For Each slotNode In slotNodes
.Cells(r, 1).Value = slotNode.getAttribute("value")
r = r 1
Next slotNode
End With
End Function
CodePudding user response：
Taking all comments on board, and building on the answer by @Professor Pantsless above here is the working code.
Sub ReadXML()
Call fnReadXMLByTags
End Sub
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Dim oXMLFile As Object
Dim MapNameNode As Object
Dim MapNameNodes As Object
Dim slotNode As Object
Dim slotNodes As Object
Set oXMLFile = CreateObject("MSXML2.DOMDocument.6.0")
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/InstrumentMap/member/list/obj/member/string")
Set MapNameNodes = oXMLFile.SelectNodes("/InstrumentMap/string")
r = 2
With mainWorkBook.Sheets("Sheet1")
For Each MapNameNode In MapNameNodes
.Cells(1, 1).Value = MapNameNode.getAttribute("value")
Next MapNameNode
For Each slotNode In slotNodes
.Cells(r, 1).Value = slotNode.getAttribute("value")
r = r 1
Next slotNode
End With
End Function