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