0

I have this code:

Dim xmlOBject As Object
Set xmlOBject = CreateObject("MSXML2.DOMDocument.6.0")
xmlOBject.async = False
xmlOBject.Load ("https://api.evemarketer.com/ec/marketstat?typeid=2268&typeid=2305&typeid=2267&typeid=2268&regionlimit=10000032")' just example URL

The xmlOBject variable contain the XML resulte.

Is there a way to convert a XML resulte to array, VBA only, without using the sheet to paste the XML resulte, also it should be dynamically, it means that no matter how many xml levels/nodes there are, so it will works with any given URL ?

The above URL should provide the this array(like the attached image without the titles):

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Study [this answer](https://stackoverflow.com/a/11325/4717755) and [this answer](https://stackoverflow.com/a/4550621/4717755) to understand how to parse the XML. – PeterT Nov 13 '19 at 14:17
  • PeterT, I didn't find in your links anything that I can use to convert XML to array. –  Nov 13 '19 at 15:14
  • Parse the XML. As you parse it, write the results into your VBA Array. You may want to first collect the results in a Dictionary if you don't know what the eventual array size will be. – Ron Rosenfeld Nov 13 '19 at 15:32
  • Ron Rosenfeld, I dont know how to do it, i'm on this issue for 7 hours, trying, without any luck. –  Nov 13 '19 at 15:58
  • What you're asking for is not a simple matter, particuarly "so it works with any given URL". If you're not an experienced programmer then this might be a good use case for hiring someone who is to do this for you. – Tim Williams Nov 13 '19 at 16:09
  • Tim Williams, if it is not a simple question I think Stack Overflow is the place to ask , if it complicated, ask here :) Additionally, you know, when you ask a question, you really dont know if the answer is simple or not, I thought maybe there is a "xmlToArray" function in some VBA library or someone here already has a code like that. –  Nov 13 '19 at 16:16
  • With little coding experience you can use the following and do minor sprucing https://stackoverflow.com/a/33311483/6241235 e.g update to run from thisworkbook and use fully qualified ranges; disable events during run and enable after and output to row 2. Have your headers already in row 1. – QHarr Nov 13 '19 at 18:23
  • Looking at the [API docs](https://api.evemarketer.com/ec/), the array you have shown is populated from multiple separate parts: attributes on the "ExecAPI" element; part of the initial query string; elements descended from the "buy" element; elements descended from the "sell" element. You could parse out the relevant parts with [XPath](https://www.w3.org/TR/1999/REC-xpath-19991116/#path-abbrev) and combine them into an array – barrowc Nov 13 '19 at 22:02
  • 1
    or just use html parser just as easily. – QHarr Nov 13 '19 at 22:27
  • You can also use `Power Query`, available in Excel 2010+, along with one of the `ExpandAll` connections available through a web search. – Ron Rosenfeld Nov 18 '19 at 11:25

0 Answers0