See if this gets you started, though untested because I don't have an API key for this:
dvsa.github.io
VBA Code:
Public Sub XMLhttp_MOT_Check()
Const APIkey = "YOUR API KEY"
Dim httpReq As Object
Set httpReq = CreateObject("MSXML2.XMLHTTP")
Dim rootURL As String, registrationEndpointURL As String
Dim registration As String
rootURL = "https://beta.check-mot.service.gov.uk"
registration = "XX10ABC"
registrationEndpointURL = rootURL & "/trade/vehicles/mot-tests?registration=" & registration
With httpReq
.Open "GET", registrationEndpointURL, False
.setRequestHeader "Accept", "application/json+v6"
.setRequestHeader "x-api-key", APIkey
.send
Debug.Print .Status, .statusText
Debug.Print .responseText
End With
End Sub
The Debug output is shown in the VBA Immediate Window. If the request is valid the responseText contains the JSON response string and you can parse this string for the "completedDate" values. I recommend parsing it with the VBA-JSON JsonConverter module at:
JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.
github.com
Hi John, thanks for the reply, that works great.
I am just struggling to work out a way to get the information from the json as it doesn't look like I will be able to use the GitHub solution as our work computers won't let me install anything to them due to the security settings. Is there a way to pull it out without? I only really need the date of the last test and if it passed or failed (the bits in red/bold/underlined below). It is pulled the result into the immediate window so it's definitely worked but obviously it's the full string.
The returned string is:
200
[{"registration":"XX10ABC","make":"LAND ROVER","model":"RANGE ROVER","firstUsedDate":"2014.03.01","fuelType":"Petrol","primaryColour":"Black","vehicleId":"Lu0QQjYE4uSd-ANBeTRXbA==","registrationDate":"2014.03.01","manufactureDate":"2014.03.01","engineSize":"4999","motTests":[{"completedDate":"
2020.02.29 12:19:35","testResult":"
PASSED","expiryDate":"2021.02.28","odometerValue":"20979","odometerUnit":"mi","motTestNumber":"505543558510","odometerResultType":"READ","rfrAndComments":[]},{"completedDate":"2019.02.13 12:00:08","testResult":"PASSED","expiryDate":"2020.02.28","odometerValue":"18779","odometerUnit":"mi","motTestNumber":"231019448972","odometerResultType":"READ","rfrAndComments":[]},{"completedDate":"2018.02.08 15:01:11","testResult":"PASSED","expiryDate":"2019.02.28","odometerValue":"15450","odometerUnit":"mi","motTestNumber":"189750419477","odometerResultType":"READ","rfrAndComments":[]},{"completedDate":"2017.02.21 10:55:01","testResult":"PASSED","expiryDate":"2018.02.28","odometerValue":"12055","od
ometerUnit":"mi","motTestNumber":"490394149614","odometerResultType":"READ","rfrAndComments":[]}]}]