Extracting data from an API

[blinks58]

I'm trying to learn how to grab data from an API and insert it into the appropriate place on an Excel spreadsheet, but I can't find any documentation that does what I want.

For learning purposes, I'm using the simplest API I could find quickly, which is Find Your MP at http://findyourmp.parliament.uk/api.

A typical request with the API would be http://findyourmp.parliament.uk/api/...PE24+4AJ&f=csv

Starting with a spreadsheet such as:

UK POSTCODE	CONSTITUENCY NAME	MEMBER NAME
AB10 1A
CH1 1DQ
PE24 4AJ

I want to fill in the CONSTITUENCY NAME and MEMBER NAME fields, thus:

UK POSTCODE	CONSTITUENCY NAME	MEMBER NAME
AB10 1A	Aberdeen North	Mr Frank Doran
CH1 1DQ	City of Chester	Stephen Mosley
PE24 4AJ	Boston and Skegness	Mark Simmonds

Any guidance would be much appreciated.

[Norie]

Here's an example, though I'm not sure it's good one.

Sub GetMPData()
' http://findyourmp.parliament.uk/api/search?q=PE24+4AJ&f=csv
Dim xml As Object
Dim varData
Dim rng As Range

    Set xml = CreateObject("MSXML2.XMLHTTP")

    Set rng = Range("A2")

    While rng.Value <> ""
    
        xml.Open "GET", "http://findyourmp.parliament.uk/api/search?q=" & Replace(rng.Value, " ", "+") & "&f=csv", False

        xml.send

        varData = Split(Replace(Split(xml.responseText, Chr(10))(1), Chr(34), ""), ",")

        rng.Offset(, 1).Value = varData(0)
        rng.Offset(, 2).Value = varData(1)
        
        Set rng = rng.Offset(1)

    Wend

End Sub

Two rows are returned the first is the headers and the 2nd the actual data.

For example:

constituency_name,member_name,member_party,member_biography_url,member_website, uri
"Boston and Skegness","Mark Simmonds","Conservative","http://www.parliament.uk/biographies/commons/mark-simmonds/25302","http://www.marksimmonds.org/","http://findyourmp.parliament.uk/constituencies/boston-and-skegness.csv"

We split those 2 rows and return the second row, which we then split to get the data.

[blinks58]

Thanks heaps, Nori, this does do exactly what I want.