Get Array from XML web page using VBA

[nd0911]

Hello,

I have this code:

Dim xmlOBject As Object


Set xmlOBject = CreateObject("MSXML2.DOMDocument.6.0")


xmlOBject.async = False
xmlOBject.Load ("https://www.boi.org.il/currency.xml")' just example URL

and its load to the xmlOBject variable the XML resulte.

Is there a way to convert a XML resulte (dynamically, no matter how many xml levels) to array ? ( i'm intrest by any URL, not just the URL provided above).

The above URL should provide the this array:

[TABLE="width: 532"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]30/10/2019[/TD]
[TD]Dollar[/TD]
[TD]1[/TD]
[TD]USD[/TD]
[TD]USA[/TD]
[TD]3.528[/TD]
[TD]-0.057[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Pound[/TD]
[TD]1[/TD]
[TD]GBP[/TD]
[TD]Great Britain[/TD]
[TD]4.5489[/TD]
[TD]0.196[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Yen[/TD]
[TD]100[/TD]
[TD]JPY[/TD]
[TD]Japan[/TD]
[TD]3.2415[/TD]
[TD]0.009[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Euro[/TD]
[TD]1[/TD]
[TD]EUR[/TD]
[TD]EMU[/TD]
[TD]3.9209[/TD]
[TD]0.194[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Dollar[/TD]
[TD]1[/TD]
[TD]AUD[/TD]
[TD]Australia[/TD]
[TD]2.4223[/TD]
[TD]0.124[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Dollar[/TD]
[TD]1[/TD]
[TD]CAD[/TD]
[TD]Canada[/TD]
[TD]2.6966[/TD]
[TD]-0.311[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]krone[/TD]
[TD]1[/TD]
[TD]DKK[/TD]
[TD]Denmark[/TD]
[TD]0.5248[/TD]
[TD]0.191[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Krone[/TD]
[TD]1[/TD]
[TD]NOK[/TD]
[TD]Norway[/TD]
[TD]0.383[/TD]
[TD]0.578[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Rand[/TD]
[TD]1[/TD]
[TD]ZAR[/TD]
[TD]South Africa[/TD]
[TD]0.2414[/TD]
[TD]-0.041[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Krona[/TD]
[TD]1[/TD]
[TD]SEK[/TD]
[TD]Sweden[/TD]
[TD]0.3632[/TD]
[TD]0.138[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Franc[/TD]
[TD]1[/TD]
[TD]CHF[/TD]
[TD]Switzerland[/TD]
[TD]3.5546[/TD]
[TD]0.234[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Dinar[/TD]
[TD]1[/TD]
[TD]JOD[/TD]
[TD]Jordan[/TD]
[TD]4.9745[/TD]
[TD]-0.068[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Pound[/TD]
[TD]10[/TD]
[TD]LBP[/TD]
[TD]Lebanon[/TD]
[TD]0.0234[/TD]
[TD]0.429[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Pound[/TD]
[TD]1[/TD]
[TD]EGP[/TD]
[TD]Egypt[/TD]
[TD]0.2182[/TD]
[TD]-0.411[/TD]
[/TR]
</tbody>[/TABLE]

 

[nd0911]

Anyone maybe knows ?

 

[Kyle123]

You'd need to walk the XML, probably recursively to populate an array of jagged nested arrays.

Honestly though, why bother? XML is designed for being machine readable, queryable and you can loop through it - it offers a much richer interface with more features than jagged arrays that are a pain in Excel.