Loop through XML File using VBA and xPath
51,597
Solution 1
Instead of using the absolute path,
node.SelectSingleNode("/FileName").Text
try using the relative path (without the /
):
node.SelectSingleNode("FileName").Text
Disclaimer: Since you haven't shown us a sample of your xml file, this is largely conjecture...
Solution 2
I had to do some parsing of my own, and after a few hours, I realized that it can get difficult to get to your data with a single XPath. There can be numerous reasons:
- Bad XML Structure
- Repetitive tags
- Etc, etc
I used a combination of XPath and builtin functions to get to my data. e.g.
strFile = "C:\MyFile.xml"
intFile = 2
Open strFile For Input As intFile
'Load XML into string strXML
While Not EOF(intFile)
Line Input #intFile, strXML
Wend
Close intFile
Dim XMLDOC As MSXML2.DOMDocument
Set objDOM = CreateObject("Msxml2.DOMDocument.6.0")
Dim xmlNodes As MSXML2.IXMLDOMNodeList
objDOM.LoadXML strXML
XPath = "/query/results"
Set xmlNode = objDOM.SelectNodes(XPath)
rowCounter = WorksheetFunction.CountA(output.Columns("A")) + 1
Set oNodes = objDOM.getElementsByTagName("quote")
If oNodes.Length > 0 Then
For Each oNode In oNodes
output.Cells(rowCounter, 1) = symbol
output.Cells(rowCounter, 2) = oNode.SelectSingleNode("Date").Text
output.Cells(rowCounter, 3) = oNode.SelectSingleNode("Open").Text
output.Cells(rowCounter, 4) = oNode.SelectSingleNode("High").Text
output.Cells(rowCounter, 5) = oNode.SelectSingleNode("Low").Text
output.Cells(rowCounter, 6) = oNode.SelectSingleNode("Close").Text
output.Cells(rowCounter, 7) = oNode.SelectSingleNode("Volume").Text
output.Cells(rowCounter, 8) = oNode.SelectSingleNode("Adj_Close").Text
rowCounter = rowCounter + 1
Next oNode
End If
You can find the Excel sheet present at my blog.
Author by
BBQ Chef
Updated on March 11, 2020Comments
-
BBQ Chef over 4 years
I’m stuck with parsing/extracting my XML with xPath in my MS Project VBA code.
Why can’t I select a node in this node?
Set nodes = xml.SelectNodes("/config/ProjectFile") For Each node In nodes With Me.lbProjList '.AddItem (xmlText(node.SelectSingleNode("/FileName"))) '.Column(1, i) = xmlText(node.SelectSingleNode("/LastSaveDate")) End With i = i + 1 Debug.Print i & " file " & node.xml ' Shows the XML I expected Debug.Print " Name: " & node.SelectSingleNode("/FileName").Text ' Doesn't work! Error 91 Next node
I’d be glad to get some help!!!
Thanks!
Here is the complete VBA code:
Private Sub ProjListFill() Dim i As Integer Dim xml As MSXML2.DOMDocument60 Dim nodes As MSXML2.IXMLDOMNodeList 'CustomXMLNodes??? Dim node As MSXML2.IXMLDOMNode 'CustomXMLNode??? Dim n As CustomXMLPart 'CustomXMLNode??? ' clear form before fill it Me.lbProjList.Clear Me.txtHeadline.value = "" Me.txtUpdateURL.value = "" Me.txtBoxParam.value = "" Me.txtBoxPrefix.value = "" Set xml = readXML(CustomProperty("XMTMLMonitoring_AppPath") & "\" & m2w_config("SubFolder") & "\" & m2w_config("SubFolderData") & "\" & m2w_config("XMLConfigFileName")) i = 0 Set nodes = xml.SelectNodes("/config/ProjectFile") For Each node In nodes With Me.lbProjList '.AddItem (xmlText(node.SelectSingleNode("/FileName"))) '.Column(1, i) = xmlText(node.SelectSingleNode("/LastSaveDate")) End With i = i + 1 Debug.Print i & " file " & node.xml ' Shows the XML I expected Debug.Print " Name: " & node.SelectSingleNode("/FileName").Text ' Doesn't work! Error 91 Next node Debug.Print i & " Project files found in config.xml" ' fill text boxes Me.txtHeadline.value = xmlText(xml.SelectSingleNode("/config/Custom/Headline")) Me.txtUpdateURL.value = xmlText(xml.SelectSingleNode("/config/Custom/UpdateURL")) Me.txtBoxParam.value = xmlText(xml.SelectSingleNode("/config/Custom/BoxParam")) Me.txtBoxPrefix.value = xmlText(xml.SelectSingleNode("/config/Custom/BoxPrefix")) ExitProjListFill: Exit Sub End Sub
Here is the XML
<config id="config" ConfigSaveDate="2011-03-31 21:32:55" ConfigSchemaVersion="1.02"> <Custom> <DateFormat>yyyy-mm-dd hh:mm:ss</DateFormat> <Headline>Project Overview</Headline> <UpdateHref></UpdateHref> <BoxParam>ModelYear</BoxParam><BoxPrefix>MY </BoxPrefix> </Custom> <Program> <DateFormat>yyyy-mm-dd hh:mm:ss</DateFormat> </Program> <ProjectFile ProjectFileName="projectfile1.mpp"> <RevisionNumber>201</RevisionNumber> <FileName>projectfile1.mpp</FileName> <LastSaveDate>2011-03-23 16:45:19</LastSaveDate> </ProjectFile> <ProjectFile ProjectFileName="projectfile2bedeleted.mpp"> <RevisionNumber>115</RevisionNumber> <FileName>projectfile2b.mpp</FileName> <LastSaveDate>2011-03-31 21:12:55</LastSaveDate> </ProjectFile> <ProjectFile ProjectFileName="projectfile2.mpp"> <RevisionNumber>315</RevisionNumber> <FileName>projectfile3.mpp</FileName> <LastSaveDate>2011-03-31 21:32:55</LastSaveDate> </ProjectFile> </config>