I have an XML file 'because.xml'. This is a MusicXML file exported from Notion (a score writing tool).
I'm trying to write VBA code to extract the data within the <part> elements, and put them on an Excel sheet in the layout in attached Excel.JPG.
I'm following the examples in J Korol's book Excel 2010 Programming by Example with this code. But having trouble modifying it to my needs.
I would appreciate any help. Please point me in the right direction. (I get a list of data, but not in the right order as per the XML file. Odd)
Sub IterateThruElements() Dim xmlDoc As MSXML2.DOMDocument60 Dim xmlNodeList As MSXML2.IXMLDOMNodeList Dim xmlNode As MSXML2.IXMLDOMNode Dim myNode As MSXML2.IXMLDOMNode ' Create an instance of the DOMDocument Set xmlDoc = New MSXML2.DOMDocument60 xmlDoc.async = False ' Load XML information from a file strPathToXMLFile = Sheets("Settings").Range("B7").Value 'the path to the XML file xmlDoc.Load (strPathToXMLFile) ' Find out the number of child nodes in the document Set xmlNodeList = xmlDoc.getElementsByTagName("*") ' Open a new workbook and paste the data Workbooks.Add Range("A1:B1").Formula = Array("Element Name", "Text") For Each xmlNode In xmlNodeList For Each myNode In xmlNode.ChildNodes If myNode.NodeType = NODE_TEXT Then ActiveCell.Offset(0, 0).Formula = xmlNode.nodeName ActiveCell.Offset(0, 1).Formula = xmlNode.Text End If Next myNode ActiveCell.Offset(1, 0).Select Next xmlNode Columns("A:B").AutoFitEnd Sub
- open the xml file with excel directly (it will prompt you to create a default schema)
- you will see the data laid out in a table that seems close to what you want
I noted that there is no 'measure' in the file but other than that it's easy to see how the data is recorded and hide appropriate columns to get to what you asked for.
What are you trying to do next?