vba code

Sub read_from_api() 
    Dim url As String, parametrs As String
    url = "https://api.company-information.service.gov.uk/company/"
    parametrs = "?q={00519500}"
    
    'send request
    Dim request As New WinHttpRequest
    request.Open "Get", url & parametrs
    'auth=(api_key,''))
    request.SetRequestHeader "api_key", "10d54202-df9c-4e72-8753-f11bh5c7c427"
    request.Send
    
    If request.Status <> 200 Then
        MsgBox request.ResponseText
        Exit Sub
    End If
    
    Dim response As Object
    Set response = JsonConverter.ParseJson(request.ResponseText)  
End Sub

Help Please

CodePudding user response：

Looking at that API specification, the spec for the request shows:

GET https://api.company-information.service.gov.uk/company/{companyNumber}

The {companyNumber} part means replace this whole thing with the company number you want to check. The {} characters are just there to show that you need to replace this placeholder with real data and those {} should not appear in the real request.

We can also see that the companyNumber is part of the path rather than being part of the query component, so we would write the request for your example company number like this (keeping the same variable names that you have used):

url ="https://api.company-information.service.gov.uk/company/00519500"
parametrs = ""

If you look at the authentication implementation for this service, you'll see that the API key should be sent via basic authentication. So rather than having an "api_key" header (which doesn't relate to this spec at all), you should have an "Authentication" header instead.

However, it's not quite as simple as just including your API key in that header. As per the page about authentication, we need to specify that we're using basic authentication, use the API key as the username and there's no need to specify a password. The page mentions RFC2617 and if you go there, you will see that you need to put a colon between the username and password then base64 encode the whole string. Even though we don't need a password, we will still have to include the colon character.

VBA doesn't have a built-in base64 encoding function but the one from this StackOverflow answer is probably the easiest to use. Just add a reference to "Microsoft XML, v3.0" and then add the code in that answer to your project (or if you prefer not to add a new reference, see this answer instead).

Using that code, we can create the "Authentication" header as follows:

request.SetRequestHeader "Authentication", "Basic " & EncodeBase64("10d54202" & ":")

(Note that I've only included part of your API key but you should obviously use the full thing)

Those changes should hopefully lead to your code working correctly.

Other points:

• I haven't tried the API key in the question to see if it works, but if it is a real key, then you should generate a new one and delete the old one. Otherwise, you are risking someone finding that key and misusing it, leading to you being rate-limited etc
• This service has a sandbox environment and, where possible, you should test against that environment to ensure your code works before using it against the production environment