Read XML into Excel using VBA

[RichardC64]

I am new to XML and am having a problem reading into Excel. What I am trying to do is get the ordernumber and each item from itemsordered. I don't have a problem with an order that only has one item, I am having a problem with an order that has multiple items.

This is what I expect to get, but I am getting the ordernumber for the next order.

ordernumber name sku
3 Exclusive Chocolate Brown Sweetheart Saltwater Sandals HSH 1402-e
3 Red Sweetheart Saltwater Sandals HSH 1404-e



This is an example of the XML file.

<orders>
<order>
<ordernumber>3</ordernumber>
<createdate>Tue, 5 May 2009 12:08:37 PDT</createdate>
<customername>Jane Doe</customername>
<form>Order Form</form>
<orderstatus>Complete</orderstatus>
<paymentstatus>Settled</paymentstatus>
<itemsordered>
<item>
<name>Exclusive Chocolate Brown Sweetheart Saltwater Sandals</name>
<sku>HSH 1402-e</sku>
<quantity>1</quantity>
<unitprice>$36.95</unitprice>
<totalprice>$36.95</totalprice>
<unitweight>0.0</unitweight>
<totalweight>0.0</totalweight>
<variations>
<variation>
<name>Sizes</name>
<choice>Childs Size 9</choice>
</variation>
</variations>
</item>
<item>
<name>Red Sweetheart Saltwater Sandals</name>
<sku>HSH 1404-e</sku>
<quantity>1</quantity>
<unitprice>$29.95</unitprice>
<totalprice>$29.95</totalprice>
<unitweight>0.0</unitweight>
<totalweight>0.0</totalweight>
<variations>
<variation>
<name>Sizes</name>
<choice>Childs Size 9</choice>
</variation>
</variations>
</item>
</itemsordered>
<subtotal>$66.90</subtotal>
<total>$76.80</total>
<charges>
<charge>
<name>Shipping to USA</name>
<price>$9.90</price>
</charge>
</charges>
<billto>
<title></title>
<firstname>Jane</firstname>
<middlename></middlename>
<lastname>Doe</lastname>
<address1>123 Main St.</address1>
<address2></address2>
<address3></address3>
<city>Anytown</city>
<stateprovince>Alabama</stateprovince>
<postalcode>35406</postalcode>
<country>United States</country>
<email>JaneDoe@home.com</email>
<dayphone>123-456-7890</dayphone>
<eveningphone>123-456-7890</eveningphone>
<company></company>
</billto>
<billing>
<method>PayPal</method>
</billing>
<shipping>
<method>Standard Shipping</method>
<shipper></shipper>
</shipping>
<answers>
<answer id="417036"></answer>
<answer id="1372874">
<question><![CDATA[Military Base Shipping:]]></question>
<answer><![CDATA[None]]></answer>
<variable><![CDATA[]]></variable>
</answer>
</answers>
</order>

</orders>

 

[PHV]

Did you try the XmlImport method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RichardC64]

Thanks for the reply. I looked at XMLImport on the msdn site, but not sure that it is what I need. My understanding of XMLImport is that is imports all the data into a worksheet.

Would it help if I posted my code?

 

[xlbo]

Can't hurt!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RichardC64]

Here is the code I am using:

' Get the root of the XML tree.
Set oRoot = oDoc.DocumentElement

' Go through all children of the root.
For Each oOrder In oRoot.ChildNodes

' Collect the attributes
Set oAttributes = oOrder.Attributes
Set oItem = oDoc.DocumentElement.ChildNodes(6)

'Loop through childnodes of the element
'to get the items ordered for each order

For itemnbr = 0 To 6

'Order Number for each order
ActiveSheet.Cells(intI, 1).Value _
= oDoc.DocumentElement.getElementsByTagName("ordernumber")(recnbr).nodeTypedValue '.ChildNodes(itemnbr).nodeName

If oItem.HasChildNodes Then

For Each Node In oItem.ChildNodes

'Items ordered
ActiveSheet.Cells(intI, intColumn).Value _
= oDoc.DocumentElement.getElementsByTagName("item")(recnbr).ChildNodes(itemnbr).nodeTypedValue

ActiveSheet.Cells(intI, 1).Value _
= oDoc.DocumentElement.getElementsByTagName("ordernumber")(recnbr).nodeTypedValue '.ChildNodes(itemnbr).nodeName


Next
End If

intColumn = intColumn + 1
Next
recnbr = recnbr + 1
cnt = cnt + 1
intColumn = 2
intI = intI + 1

Next


I am stuck and can't this figure out.