Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Traversing an XML file with Excel VBA
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)
Thanks!
Because.xml
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)
Thanks!
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").AutoFit
End Sub
Because.xml
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Its not entirely clear what you are trying to achieve but try this:
- 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?
because2.xlsx
- 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?
because2.xlsx
regmigrant, I have done something similar already. That then needs a massively complex process to convert it to the layout I require. So what I'm trying to do (in this question) is to make that step robust by going straight from reading the DOM with VBA to the layout that I require (see attached JPG above). Thus reducing the number of moving parts.
FYI - the eventual solution I'm working on is to programmatically create a 'visual score' in Excel (for print layout) and in PowerPoint (which I will use to make a scrolling graphical score' in conjunction with video authoring software). This needs the source data to be in a sort of 'MIDI-message' layout, as in the example in the JPG. ie, time, note value, length, which part plays it.
FYI - the eventual solution I'm working on is to programmatically create a 'visual score' in Excel (for print layout) and in PowerPoint (which I will use to make a scrolling graphical score' in conjunction with video authoring software). This needs the source data to be in a sort of 'MIDI-message' layout, as in the example in the JPG. ie, time, note value, length, which part plays it.
Ok - the VBA you quoted is providing a very limited view of the data set because it is parsing only certain node types. To modify it we will need an understanding of the XML Schema used by the software which created the output - is that available?
in particular the section:
Is taking a very simplistic approach to deciding which nodes to display - ie: the ones where NODE_TEXT (an attribute of xmlNode) is the same as the NodeType, with the schema we would be able to identify specific nodes of interest and extract them appropriately because it would, for example, explain what NodeType are included and what they mean
in particular the section:
If myNode.NodeType = NODE_TEXT Then
ActiveCell.Offset(0, 0).Formula = xmlNode.nodeName
ActiveCell.Offset(0, 1).Formula = xmlNode.Text
End If
Is taking a very simplistic approach to deciding which nodes to display - ie: the ones where NODE_TEXT (an attribute of xmlNode) is the same as the NodeType, with the schema we would be able to identify specific nodes of interest and extract them appropriately because it would, for example, explain what NodeType are included and what they mean
regmigrant, now it's getting exciting! Thank you.
The MusicXML schema is here http://www.musicxml.com/for-developers/musicxml-xsd/
The MusicXML schema is here http://www.musicxml.com/for-developers/musicxml-xsd/
that helps
The simplest way to achieve this is to open the file with Excel and when prompted ask it to generate a Schema Map. you can then drag and drop the individual elements into the spreadsheet as table headings. When you are done right click and choose xml, import data, then pick the same file and you will get the something like the attached.
Unfortunately you can't save the schema with the excel file because its too complex, if you want a programmatic way to interpret it that is completely robust you will need to use the data in the website to understand the structure and develop a routine that can walk the list of lists and maintain the relationships between elements. I suggest you may get more mileage from a full blown XML editor that will allow you to add the mapping to an excel file and script the transform you are looking for.
that's a bigger job than I am willing to support via EE - sorry
because3.xlsx
The simplest way to achieve this is to open the file with Excel and when prompted ask it to generate a Schema Map. you can then drag and drop the individual elements into the spreadsheet as table headings. When you are done right click and choose xml, import data, then pick the same file and you will get the something like the attached.
Unfortunately you can't save the schema with the excel file because its too complex, if you want a programmatic way to interpret it that is completely robust you will need to use the data in the website to understand the structure and develop a routine that can walk the list of lists and maintain the relationships between elements. I suggest you may get more mileage from a full blown XML editor that will allow you to add the mapping to an excel file and script the transform you are looking for.
that's a bigger job than I am willing to support via EE - sorry
because3.xlsx
Hi regmigrant, that's helpful.
I should also have said, I'm using this example to learn how to loop through the elements of an XML file to extract data into a 'flat file' structure.
The idea of using the schema to know what to expect is one that I hadn't thought of (though that's the purpose of the schema. Doh me!).
I shall experiment.
I should also have said, I'm using this example to learn how to loop through the elements of an XML file to extract data into a 'flat file' structure.
The idea of using the schema to know what to expect is one that I hadn't thought of (though that's the purpose of the schema. Doh me!).
I shall experiment.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial