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:

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

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

 

[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.