How to Convert XML text intoe Array

[colonna]

I have the next problem.
In one Sheet i have :



<?xml version="1.0"?>

<hash>

<cif>1343490</cif>

<address>Str. G-ral Ion Dragalina Nr. 18</address>

<city>Ploiești</city>

<fax>0244510325</fax>

<name>24 Ianuarie S.A.</name>

<phone>0244526350</phone>

<registration-id>J29/1043/1991</registration-id>

<authorization-number nil="true"/>

<state>Prahova</state>

<vat>1</vat>

<zip>100157</zip>

<created-at type="dateTime">2012-03-26T11:13:55+00:00</created-at>

<updated-at type="dateTime">2014-08-28T18:26:49+00:00</updated-at>

</hash>

and i need to convert the XML into Excel Array on shhet 2.
How can i achive this fast .?

[snb]

Excel / Ribbon / Developer / Import

[colonna]

:)i have in Sheet 1 , Column A... a lot of lines like the one atached. And i need to use VBA to convert them in Array. And the Array to be writen in Sheet2. Sheet2 should look like this :
1343490|Str. G-ral Ion Dragalina Nr. 18|Ploiești|0244510325|J29/1043/1991 ................... and so on. I know the DomDocument can be used... But don`t know how......

[Kenneth Hobs]

Maybe save the code to an xml first and then something like: http://www.vbaexpress.com/forum/showthread.php?t=41961

[colonna]

Hello , i`ve managed to resolve it like this Sub app()
Dim c As Range
Dim nume
Dim RO
Dim cif
Dim adresa
Dim city
Dim state
Dim zip
Dim vat
Dim phone
Dim j

For Each c In Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
With Worksheets("Sheet1")

Dim req As New XMLHTTP

req.Open "GET", "http://openapi.ro/api/companies/" & Sheets("Sheet1").Range("A" & c.Row).Value & ".xml", False
req.send
Dim resp As New DOMDocument
resp.LoadXML req.responseText



nume = resp.getElementsByTagName("name").Item(0).Text
Sheets("Sheet2").Range("A" & c.Row).Value = nume



RO = "RO"

Sheets("Sheet2").Range("F" & c.Row).Value = RO


cif = resp.getElementsByTagName("cif").Item(0).Text


Sheets("Sheet2").Range("G" & c.Row).Value = cif



adresa = resp.getElementsByTagName("address").Item(0).Text


Sheets("Sheet2").Range("B" & c.Row).Value = adresa


city = resp.getElementsByTagName("city").Item(0).Text


Sheets("Sheet2").Range("C" & c.Row).Value = city



state = resp.getElementsByTagName("state").Item(0).Text


Sheets("Sheet2").Range("D" & c.Row).Value = state



zip = resp.getElementsByTagName("zip").Item(0).Text


Sheets("Sheet2").Range("E" & c.Row).Value = zip



vat = resp.getElementsByTagName("vat").Item(0).Text


Sheets("Sheet2").Range("K" & c.Row).Value = vat



phone = resp.getElementsByTagName("phone").Item(0).Text


Sheets("Sheet2").Range("M" & c.Row).Value = phone


j = resp.getElementsByTagName("registration-id").Item(0).Text


Sheets("Sheet2").Range("N" & c.Row).Value = j


End With
Next c
End Sub


BUT NOW , WHEN DATA IS EMPTY = I HAVE A RUNTIME ERROR Runtime Error "91"
Object or variable .... etc

How can i solve this ???

[snb]

Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://openapi.ro/api/companies/13548146.json", False
.send
sn = Filter(Split(Replace(.responseText, ":""", ":"","""), ""","""), ":", False)
Cells(1, 1).Resize(, UBound(sn) + 1) = sn
End With
End Sub