Get required Data from API based on Cell

[kachaloo]

Hello all
this is my first ever attempt to fiddle with APIs.

I have vehicle registrations in Column-A cells and I want to use the UK, DVLA vehicle enquiry service API to get the "make" and "colour" in make and colour columns in excel.
I have already applied for the API and have my own API now to make requests.

My goal is to add a button and only get data when required.

Registration	GetData	Make	Colour
WR60HRG

There are few examples on the page to get the data. The responses are returned in JSON. that I need to parse and get the required data in make and colour cells. Any pointers.
Regards

The API page with examples.

DVLA Vehicle Enquiry Service (VES) API Guide

developer-portal.driver-vehicle-licensing.api.gov.uk

 

[John_w]

See if you can adapt the code I posted for a similar question for a different API here:

Get information from API using VBA

I am trying to pull information from an API but I can't work out how to do the coding. I have been provided the below in relation to the API and I have my API key to add in but would appreciate it if someone could assist with how I need to code it. curl -H "Accept: application/json" -H...

www.mrexcel.com

Change the "GET" to "POST" and the .send to:

    Dim registration As String, POSTdata As String
    registration = "WR60HRG"
    POSTdata = "{""registrationNumber"":""" & registration & """}"
   
        .send (POSTdata)

You could parse the response with Instr and Mid functions to extract the required data; the JsonConverter mentioned in that thread is probably overkill for this API.

 

[kachaloo]

See if you can adapt the code I posted for a similar question for a different API here:

Get information from API using VBA

I am trying to pull information from an API but I can't work out how to do the coding. I have been provided the below in relation to the API and I have my API key to add in but would appreciate it if someone could assist with how I need to code it. curl -H "Accept: application/json" -H...

www.mrexcel.com

Change the "GET" to "POST" and the .send to:

    Dim registration As String, POSTdata As String
    registration = "WR60HRG"
    POSTdata = "{""registrationNumber"":""" & registration & """}"
  
        .send (POSTdata)

You could parse the response with Instr and Mid functions to extract the required data; the JsonConverter mentioned in that thread is probably overkill for this API.

Apologies for the late reply. did not notice a reply from you.
I have not used VBA only normal excel. would you please have spare time to send me an example sheet?

Thanks again for your reply and I still going to read your post to see if I can make it work .

 

[John_w]

I have not used VBA only normal excel. would you please have spare time to send me an example sheet?

No, I won't send you anything, but see if the following macro works for you. Obviously untested because I don't have an API key to test it - edit the code where shown to include your API key.

Public Sub Get_Vehicle_Data()

    Const APIkey = "YOUR API KEY HERE"    'EDIT THIS
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    
    Dim URL As String
    Dim registration As String, POSTdata As String
    Dim JSON As Object
    
    URL = "https://driver-vehicle-licensing.api.gov.uk/vehicle-enquiry/v1/vehicles"
    
    registration = "WR60HRG"
    
    POSTdata = "{""registrationNumber"":""" & registration & """}"
    
    With httpReq
        .Open "GET", URL, False
        .setRequestHeader "x-api-key", APIkey
        .setRequestHeader "Accept", "application/json"
        .send POSTdata
        Debug.Print .Status, .statusText
        Debug.Print .responseText
        Debug.Print GetField(.responseText, "make")
        Debug.Print GetField(.responseText, "colour")
    End With
        
End Sub


Private Function GetField(JSONstring As String, field As String) As String

    Dim p1 As Long, p2 As Long
    
    p1 = InStr(1, JSONstring, Chr(34) & field & Chr(34), vbTextCompare)
    If p1 > 0 Then
        p1 = p1 + Len(field) + 2
        p1 = InStr(p1, JSONstring, """") + 1
        p2 = InStr(p1, JSONstring, """")
        GetField = Mid(JSONstring, p1, p2 - p1)
    Else
        GetField = field & " not found"
    End If

End Function