0

I have been able to modify some code found on here to pull some data from an XML file but I am struggling to do this for one particular number, I am very new to XML files and cant figure out if what I am trying to pull is an Attribute/Node/Element. Below is the XML and the VBA I have so far.

<?xml version="1.0" encoding="UTF-8"?>
<reel id="r1" protversion="17">
    <order number="81234">
        <job name="apples">
            <lengthunit>mm</lengthunit>
            <formatlength>570.0</formatlength>
            <formatwidth>1300.0</formatwidth>
        </job>
    </order>
</reel>

Function ReadFromXML2()

Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "E:\xml files\report1.xml"
oXMLFile.Load (XMLFileName)

Set Nodes_Attribute = oXMLFile.SelectNodes("reel")
For i = 0 To (Nodes_Attribute.Length - 1)
    Attributes = Nodes_Attribute(i).getAttribute("id")
    ThisWorkbook.Worksheets(1).Range("A" & i + 2).Value = Attributes
Next

Set TitleNodes = oXMLFile.SelectNodes("/reel/order/job/formatlength/text()")
For i = 0 To (TitleNodes.Length - 1)
    Title = TitleNodes(i).NodeValue
    ThisWorkbook.Worksheets(1).Range("B" & i + 2).Value = Title
Next

End Function

The part I am struggling with is the order number, should I be using getAttribute or NodeValue? Whay I have so far works for getting the reel ID and formatlength.

J.Goddard
  • 107
  • 3
  • 11
  • 2
    Always use `Option Explicit` in the declaration head of code modules to force variable declarations and consider `MSXML2` instead of `Microsoft.XMLDOM` which has been deprecated for years. - This post might give you some further ideas: [What's the XPath syntax to get tag names (including attributes)](https://stackoverflow.com/questions/65284905/vba-whats-the-xpath-syntax-to-get-tag-names/65289499#65289499) – T.M. Jan 22 '21 at 17:29
  • Variables have been added properly now I have something working. Would this be as simple as changing ```Microsoft.XMLDOM``` to ```MSXML2``` in the code of will there different syntax completely? – J.Goddard Jan 22 '21 at 17:38
  • 1
    Take the time to study the referred link(s). If you are using **late binding** as in OP (after dimensioning all your nodes etc `.. As Object` - you know objects have to be `Set`) write: Example: `Dim oXMLFile As Object` and `Set oXMLFile = CreateObject("MSXML2.DOMDocument.6.0") – T.M. Jan 22 '21 at 17:50
  • I have followed the links, I am attempting to use early binding, I have the put something together but I am now getting runtime error 91, object variable or with block variable not set, but am unsure what can cause that in this context – J.Goddard Jan 22 '21 at 18:03
  • 1
    If you are sure that in the referred DocumentElement named ` exists an attribute with name `id` you can write as follows: `Worksheets(2).Range("A" & LastRow + 1).Value = qXML(i).Attributes.getNamedItem("id").Text` ` – T.M. Jan 22 '21 at 18:21
  • Thanks that sorted it, I was after attribute not node. Thanks. Is there a way to do this for several attributes without having to do ```Set qXML = xmlFile.SelectNodes("reel")``` each time? The data is in the same node, just one level down and is also an attribute (I think) – J.Goddard Jan 22 '21 at 18:25
  • 1
    VBA knows a `With ...` ... `End With` structure by which you can abbreviate your references. Think, however that this has nothing to do with the original question - better to ask a new precise question. - Just try the further references I indicated in the mentioned link including a function to list attributes and showing recursive calls; if you found them helpful feel free to upvote them. – T.M. Jan 22 '21 at 18:47
  • Thanks, I understand – J.Goddard Jan 22 '21 at 18:53

1 Answers1

0

Figured it out, I used getAttribute but I was trying "order number" rather than just "number"

J.Goddard
  • 107
  • 3
  • 11