Convert CURL Statement to VBA

[wideboy]

Hi,

I have a working CURL statement that I need to convert to VBA. The statement is (with sensitive items replaced):

curl -H "Accept: application/json" -H "x-api-key: MyAPIKey" https://beta.check-mot.service.gov.u...ration=XX99XXX -o "output.txt"

The VBA code I have so far is:

Public Sub SendHttpPostRequest()
    
    Dim URL, Key, RequestBody As String
    Dim Request As Object
    
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")

    ' Set the URL for the HTTP POST request
    URL = "https://beta.check-mot.service.gov.uk/trade/vehicles/mot-tests"
    
    ' Set the KEY for the HTTP request with the POST method
    Key = "MyAPIKey"

    ' Set the request body (your data to be sent)
    RequestBody = "registration=XX99XXX"

    ' Open the HTTP request with the POST method
    Request.Open "POST", URL, False

    ' Set the request headers
    Request.setRequestHeader "Content-Type", "application/json"
    Request.setRequestHeader "Accept", "application/json"
    Request.setRequestHeader "Authorization", Key
        
    ' Send the request with the request body
    Request.send 'RequestBody

    ' Check for a successful response (status code 200)
    If Request.Status = 200 Then
        Debug.Print "Request successful. Response text: " & Request.responseText
    Else
        Debug.Print "Request failed. Status code: " & Request.Status & ", Response text: " & Request.responseText
    End If

End Sub

I am an extremely novice programmer and have only gotten this far based on many internet searches and ChatGPT. I feel the error is in the Authorization request but all the service provider says is to use "x-api-key". References for Scripting Runtime, XML and WinHTTP Services are all enabled.

I get the error "Authorization header requires 'Credential' parameter. Authorization header requires 'Signature' parameter. Authorization header requires 'SignedHeaders' parameter. Authorization header requires existence of either a 'X-Amz-Date' or a 'Date' header. Authorization=MyAPIKey" when I try to run it.



The output to a file has not been implemented in that code but suggestions welcomed on how to do it.

Thanks in advance.

[wideboy]

I have been able to use a workaround through the Call Shell function to run the curl command from the Command Prompt & only download successful results. Going to leave this here for any person who may need it in the future.

Call Shell("cmd /c" & "curl -s -S -f -H ""Accept: application/json"" -H ""x-api-key: MyAPIKey"" https://beta.check-mot.service.gov.uk/trade/vehicles/mot-tests?registration=" & Reg & " --output ""C:\CNV\Data\JSON\" & Reg & ".json""", vbHide

[pbaldy]

Thanks for updating the thread with your solution and welcome to the site! Hopefully we can be more help in the future.