How to extract data from XML file for import into Excel

[olympus41]

Hello. I have never dealt with .XML files before until now. All I am trying to do is extract the data from this .xml file and import into Excel and display the SKU, Part, Qty, etc. of each item in their own columns/row. I have searched the web and cannot seem to find an easy solution. Below are some of the contents of the .xml file. The entire .xml follows this same format.

-------------- Beginning -------------------
<?xml version="1.0" encoding="UTF-8" ?>
- <AvailableBatch>
<Date>12/17/2010</Date>
<UDT>1292603880</UDT>
<Type>Changes</Type>
- <Available>
<Sku>5290</Sku>
<Part>AA05BK</Part>
<Qty>107</Qty>
<Time>11:38:18</Time>
</Available>
- <Available>
<Sku>5390</Sku>
<Part>AA20BK</Part>
<Qty>589</Qty>
<Time>11:38:18</Time>
</Available>
- <Available>
<Sku>6120</Sku>
<Part>AA68</Part>
<Qty>337</Qty>
<Time>11:38:18</Time>
</Available>
- <Available>
<Sku>7500</Sku>
<Part>AB44</Part>
<Qty>30</Qty>
<Time>11:38:18</Time>
</Available>
-------------- END -------------------

 

[Diancecht]

Did you do a search on excel xml import? You will find info like this

Cheers,
Dian

 

[tsuji]

I've long time since "refused" to upgrade my office suite, so I cannot adequately advise on the gui aspect of it. In the meantime, I believe they cannot continue to make fake progress with egoistic and anti-competition purposes to the point that they cannot themselves even adequately explain the limitation and the how-to of the working through their gui. The reason is simply that it is not ever enough to settle the use of a series technologies by reduction to handful instruction rules to their gui users.

I can write this simple macro workable even in office 97. I script it even in late binding for the more who don't even care about early binding in a rad environment. If that inspires you to ever make a solution independently of those advertised functionalities, which looked sophisticated and powerful but, in fact, with serious limitation and unwarranted rigidity, that would serve my purpose.
[tt]
Sub importxml()
[green]'givens
spath="abc.xml" 'append path if that is not the same as the xls[/green]

Set oparser = CreateObject("msxml2.domdocument.6.0")
With oparser
.async = False
.resolveexternals = True
.validateonparse = False
.setproperty "SelectionLanguage", "XPath"
bret = .Load(spath)
End With
If Not bret Then
MsgBox oparser.parseerror.errorcode & vbCrLf & oparser.parseerror.reason
Set oparser = Nothing
Exit Sub
End If
Set cnodes = oparser.selectnodes("//Sku")
With ActiveWorkbook.ActiveSheet
For i = 0 To cnodes.Length - 1
.Cells(i + 1, 1).Value = eff_data(cnodes(i))
.Cells(i + 1, 2).Value = eff_data(cnodes(i).selectsinglenode("following-sibling:art"))
.Cells(i + 1, 3).Value = eff_data(cnodes(i).selectsinglenode("following-sibling::Qty"))
.Cells(i + 1, 4).Value = eff_data(cnodes(i).selectsinglenode("following-sibling::Time"))
Next
End With
Set cnodes = Nothing
Set oparser = Nothing
End Sub

Function eff_data(onode)
If onode Is Nothing Then
eff_data = ""
Else
eff_data = onode.Text
End If
End Function
[/tt]