Trying to retrieve xml data from a website through excel

Question

I am writing an excel spreadsheet for use with a game called Eve Online. My spreadsheet needs to automatically retrieve data through a data provider website called eve-marketdata.com. On the developer page of eve-marketdata: http://eve-marketdata.com/developers/item_history2.php it gives some instructions on how to request data by sending a 'post' request through http.

For instance: The url used for a market history request would be: http://api.eve-marketdata.com/api/item_history2.xml?char_name=venoushka patel then, lets say I want to retrieve 45 days of data for item id 34, from region 10000002. I would put at the end of the url: item_id=34, days=45 and region_ids=10000002 each separated by the & symbol for a finished product of: api.eve-marketdata.com/api/item_history2.xml?char_name=venoushka patel&type_ids=34&region_ids=10000002&days=45 (removed the http:// due to posting regulations on this site)

So, I need certain cells to use the info in the spreadsheet such as item ids, region ids, etc, to parse together the appropriate url, submit the url to the site and then grab only the info that I am looking for and place it into a cell. This is currently much easier on google docs instead of excel because of their 'importxml' feature utilizing xpath. However, google docs limit the number of xml calls to 50 per spreadsheet and my spreadsheet is going to have a few thousand, so I HAVE to use excel.

And no, I am not going to use the 'import from web' or 'map xml' to cells features in excel because I have thousands of these to do in a spreadsheet, not time or resource efficient.

I've been told that this can be done using the following vba code:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

The problem is, although i'm very good at programing excel spreadsheets, I have never used VBA before. I know how to open the visual basic editor but that is about it. First off:

How would I paste this into the vba editor and then call upon it in my workbook? I believe I would have to put something like function () at the beginning and end function at the end, right?

How would I call upon this function in my worksheet to do what I need it to do?

Could someone explain what the above code does, and how I need to modify it to perform my task?

In advance, I REALLY APPRECIATE any help received on this and there is a huge part of the community (thousands out there) that need a way to do this as well!

Answer 1

You wouldn't do it quite like that. Firstly, the request is GET and not POST, so that changes things. You can use something like the below:

    Function ImportData(sUrl As String) As Variant
        Dim oDoc As Object
        Dim oAttr As Object
        Dim oRow As Object
        Dim x As Long: x = 1
        Dim y As Long: y = 1
        Dim vData() As Variant

        Set oDoc = CreateObject("MSXML2.DOMDocument")
        With oDoc
            .Load sUrl
            Do: DoEvents: Loop Until .readystate = 4
            ReDim vData(1 To .getElementsByTagName("row").Length, 1 To 8)
            For Each oRow In .getElementsByTagName("row")
                For Each oAttr In oRow.Attributes
                    vData(x, y) = oAttr.Text
                    y = y + 1
                Next oAttr
                y = 1
                x = x + 1
            Next oRow
        End With

        ImportData = vData

    End Function


    Public Sub Test()
        Dim g
        'g = ImportData("http://api.eve-marketdata.com/api/item_history2.xml?char_name=venoushka%20patel&type_ids=34&region_ids=10000002&days=45")
        'or
        g = ImportData(Sheets(1).range("a1").Value)
        Sheets(1).Cells(2, 1).Resize(UBound(g), UBound(g, 2)).Value = g
    End Sub

You can test it by putting it in a normal module then running the macro Test you can see where you'd put your url. I have to admit though, you might be up for a steep learning curve with this one, it's not going to be the easiest project to cut your VBA teeth on