0

I'm having trouble retrieving the node value of 1 XML file. For all other XML files it workes except for this one. I already checked if the XML get loaded and it does. I can find the values in the locals window so it is defenitely reading the XML file. The problem it retrieving the node value for this one.

The code that I used for the other files:

Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = Dir(L_Folder & "\*" & Control & "*.xml")

oXMLFile.Load L_Folder & "\" & XMLFileName


Set NameNode = oXMLFile.SelectNodes("/DataSetCollection/DataSet/Data/Lot/LotRunGenKey")

MsgBox NameNode(i).NodeValue

for the other XML files I added /text() at the end because there was an extra line with text for every nodevalue.

Example of the XML file enter image description here

<?xml version="1.0"?>
<DataSetCollection xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <DataSet Name="" Type="" Version="11.2.0" CreateTime="20200220190104">
    <Data IsVirtual="false">
      <Lot ID="" LotRunGenKey="4bccbfaf-0868-4240-a94f-724d9a79b102" ARPFlag="1" />
Wesley
  • 190
  • 9
  • Your code seems incomplete? What is `i` here? And what exactly happens when you run your code - do you get an error? If Yes what is it? – Tim Williams Apr 04 '20 at 21:31
  • It didn't work in the other script when I didn't put the `i`in. It is actually just `0` The error is on the `msgbox` line "object variable or with block variable not set" I had simular errors in my previous XML files when I made a typo in the `SelectNodes` string. @Partfait I added the code as code. Is this what you mean? – Wesley Apr 04 '20 at 21:59
  • fyi if I hover over the msgbox it returns "empty" – Wesley Apr 04 '20 at 22:03
  • 1
    Using "Microsoft.XMLDOM" is not best practice - see [here](http://web.archive.org/web/20190127003248/https://blogs.msdn.microsoft.com/xmlteam/2006/10/23/using-the-right-version-of-msxml-in-internet-explorer/). Try using "Msxml2.DOMDocument.6.0" instead, but note that this will switch the `SelectNodes` statement to using XPath instead of the XSL Patterns that your current code is using by default - see [this answer](https://stackoverflow.com/a/6022185/2127508) for the main differences. Note that the first item in an XPath collection is item 1, whereas XSL Patterns starts collections at item 0 – barrowc Apr 04 '20 at 23:22
  • hmm, thanks for the heads up, I will keep that in mind when I need to use XML again. For now I will stick to the XMLDOM, I have ore than 100 nodes retrieved so changing them all would be a lot of work – Wesley Apr 05 '20 at 06:42
  • I agree with barrowc in using the current version 6.0 of MSXML2 FYI - you might be interested in some additional hints to XMLDom and structured parsing of all nodes and attributes; see [Obtain attribute names from XML using VBA](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182) and regarding additional hints to loading & XPath via MSXML2 at [XML Parse](https://stackoverflow.com/questions/53558110/xml-parse-vba-excel-function-trip-msxml2-domdocument/53559474#53559474) – T.M. Apr 05 '20 at 08:07

2 Answers2

5

Because LotRunGenKey is an attribute and not an XML node, use the .Attributes property:

Set NameNode = oXMLFile.SelectNodes("/DataSetCollection/DataSet/Data/Lot")

MsgBox NameNode(0).Attributes.getNamedItem("LotRunGenKey").Text
Parfait
  • 104,375
  • 17
  • 94
  • 125
4

As @Parfait notes a matter of element vs. attribute

Set oXMLFile = CreateObject("Microsoft.XMLDOM")
oXMLFile.LoadXML Range("A1").Value 'for testing
Set NameNode = oXMLFile.SelectNodes("/DataSetCollection/DataSet/Data/Lot/@LotRunGenKey")
MsgBox NameNode(0).text
Tim Williams
  • 154,628
  • 8
  • 97
  • 125