Excel VBA - Importing Data from a Webpage Table

Question

The site http://api.eve-central.com/api/marketstat?typeid=34&usesystem=30000142 allows developers to pull data from their site in a format such as below:

<evec_api version="2.0" method="marketstat_xml">
    <marketstat>
        <type id="34">
            <buy>
                <volume>22949619736</volume>
                <avg>4.61</avg>
                <max>4.76</max>
                <min>1.72</min>
                <stddev>0.64</stddev>
                <median>4.66</median>
                <percentile>4.75</percentile>
            </buy>
            <sell>
                <volume>18253017309</volume>
                <avg>6.01</avg>
                <max>11.87</max>
                <min>4.79</min>
                <stddev>1.23</stddev>
                <median>6.09</median>
                <percentile>4.81</percentile>
            </sell>
            <all>
                <volume>41214748156</volume>
                <avg>5.23</avg>
                <max>11.87</max>
                <min>0.26</min>
                <stddev>1.32</stddev>
                <median>4.75</median>
                <percentile>4.31</percentile>
            </all>
        </type>
    </marketstat>
</evec_api>

If I use the "Microsoft Excel 2010" -> "Data" -> "From Web" wizard then everything is formatted in a way that is easy to use, but I want to Query the results using a "For Each" loop in VBA. I tried using this to test a single result:

Sub URL_Static_Query()
    Set QT = ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://api.eve-central.com/api/marketstat?typeid=34&usesystem=30000142", _
    Destination:=Range("A1"))
    With QT
        .EnableEditing = False
        .BackgroundQuery = True
        .Refresh BackgroundQuery:=False
        .SaveData = False
    End With
End Sub

When I call this function it is formatted in a much different way than using the wizard. It makes it much more difficult to handle the information, especially when querying from several different webpages on the same sheet.

My main goal is to only pull the buy and sell results without any column or row headers; those I can enter in manually. Any help will be greatly appreciated, thanks!

That API returns XML data. Have you considered using anXML object to retrieve and process the data, such as `MSXML2.DOMDocument60` — chris neilsen, Jan 18 '14 at 09:22
Have you seen [THIS](http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards)? — Siddharth Rout, Jan 18 '14 at 15:04

score 0 · Accepted Answer · answered Jan 19 '14 at 23:28

After hours and hours of searching I finally found the answer to my question from http://www.mrexcel.com/forum/excel-questions/697000-importing-web-xml-data.html.

The code is:

 ActiveWorkbook.XmlImport URL:= _
        "http://api.eve-central.com/api/marketstat?typeid=34&usesystem=30000142", _                            
        ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$A$1")

Thank you chis neilsen for your suggestion! It did help me eventually find what I needed.

Excel VBA - Importing Data from a Webpage Table

1 Answers1