# API UK Establishment companies

**URL:** <http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437>\
**Category:** API Question\
**Created:** [October 7, 2021, 10:54am UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437 "2021-10-07T10:54:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/c/7bcc69/32.png) [@carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/u/carlos_nunesdesousa)\
**Post date:** [October 7, 2021, 10:54am UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/1 "2021-10-07T10:54:05Z")

</div>

HI ,

I have a VBA macro in excel to extract some fields for the UK Establishment companies.

The company Profile Path is working fine  
GET [https://api.company-information.service.gov.uk/company/{companyNumber}](https://api.company-information.service.gov.uk/company/%7BcompanyNumber%7D)

But when I change to the UK Establishment doesn’t get any data  
GET [https://api.company-information.service.gov.uk/company/{company\_number}/uk-establishments](https://api.company-information.service.gov.uk/company/%7Bcompany_number%7D/uk-establishments)

Is anyone experience the same or could point me on the right direction if I am not using it correctly, please?

Full VBA Code Below  
Any help is much appreciated

Thanks,  
Carlos

---

<div class="post-metadata">

**Author:** ![carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/c/7bcc69/32.png) [@carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/u/carlos_nunesdesousa)\
**Post date:** [October 7, 2021, 10:55am UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/2 "2021-10-07T10:55:22Z")

</div>

Public Sub GetCompaniesHouseData()

'Get API credentials first  
Call SetAuth

```
Dim inputWs As Worksheet
Dim LastInputRowWithData As Integer
Dim i As Integer
Dim RegNo As String
Dim OrigCompName As String

Set inputWs = Sheets("Input")
LastInputRowWithData = inputWs.Range("A1").End(xlDown).Row

Sheets("Main").Cells(6, 3).Value = 0
Sheets("Main").Cells(6, 5).Value = LastInputRowWithData

Dim OutputWs As Worksheet
Dim http As Object
Dim JSON As Object
Dim Path As String

Set OutputWs = Sheets("Output")
Set http = CreateObject("MSXML2.XMLHTTP")
  
For i = 2 To LastInputRowWithData
     
     OrigCompName = inputWs.Cells(i, 2).Value
     RegNo = inputWs.Cells(i, 1).Value
     If IsNumeric(RegNo) = True Then
        RegNo = Format(RegNo, "00000000")
     End If
     
     'MsgBox (LastInputRowWithData)
     
     Path = "https://api.company-information.service.gov.uk/company/" & RegNo & "/uk-establishments"
    'Path = "https://api.company-information.service.gov.uk/company/" & RegNo (THIS ONE IS WORKING FINE!)
    
 
     'MsgBox (Path)
     
     http.Open "GET", Path, False
     
     http.setRequestHeader "Authorization", _
         "Basic " + Base64Encode(BasicAuthUser + ":" + BasicAuthPwd)
     
     http.Send
     
     CheckHTML = InStr(http.responseText, "<html>")
     NotFound = InStr(http.responseText, "company-profile-not-found")
     
     If CheckHTML > 0 Or NotFound > 0 Then
        OutputWs.Cells(i, 1).Value = RegNo
        OutputWs.Cells(i, 2).Value = OrigCompName
        OutputWs.Cells(i, 3).Value = "Not found"
        OutputWs.Cells(i, 4).Value = ""
        OutputWs.Cells(i, 5).Value = ""
        OutputWs.Cells(i, 6).Value = ""
        OutputWs.Cells(i, 7).Value = ""
      Else:
        Set JSON = ParseJson("[" & http.responseText & "]")
        On Error Resume Next
        For Each Item In JSON
            OutputWs.Cells(i, 1).Value = Item("company_number")
            OutputWs.Cells(i, 2).Value = OrigCompName
            OutputWs.Cells(i, 3).Value = "Found"
            OutputWs.Cells(i, 4).Value = Item("company_name")
            OutputWs.Cells(i, 5).Value = Item("company_status")
            OutputWs.Cells(i, 6).Value = Item("locality")
            OutputWs.Cells(i, 7).Value = Item("type")
   
        Next
      End If
     
     'Pause 1 second so as not to exceed API rate limit
     'Application.Wait (Now + TimeValue("00:00:00"))
     Application.Wait (Now + TimeSerial(0, 0, 1) / 10)
     
     
     'Update Progress
     Sheets("Main").Cells(6, 3).Value = i
     
Next

```

End Sub

---

<div class="post-metadata">

**Author:** ![voracityemail](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/v/b77776/32.png) [@voracityemail](http://forum.companieshouse.gov.uk/u/voracityemail)\
**Post date:** [October 7, 2021, 12:43pm UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/3 "2021-10-07T12:43:06Z")

</div>

Which companies are you looking up?

Companies with the “uk-establishments” endpoint are “foreign companies” - they have their own special company numbers e.g. they start with “FC”. In fact Companies House will have one “FC” and then a number of entries for the actual “UK establishment” starting with “BR”. [The documentation shows what to expect back](https://developer-specs.company-information.service.gov.uk/companies-house-public-data-api/reference/uk-establishments/company-uk-establishments). Here’s an example I just requested via curl (I’ve cut some data for clarity):

```
curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/FC022268"
{
"registered_office_address" : { ... "country" : "United States" },
"company_number" : "FC022268",
"type" : "oversea-company",
"company_name" : "PRINCETON UNIVERSITY PRESS",
"links" : {
     "uk_establishments" : "/company/FC022268/uk-establishments",
     ...
},
"foreign_company_details" : { ... },
"external_registration_number":"EIN 21-0634483",
....
}

```

Now look at the links.uk\_establishments:

```
curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/FC022268/uk-establishments"
{
    "items": [
        {
            "company_number" : "BR011501",
            "company_name":"PRINCETON UNIVERSITY PRESS",
            "company_status":"open",
            "links": { "company" : "/company/BR011501" }
        }
    ],
    "kind":"related-companies",
    ...
}

```

As you can see it’s possible to have one or more (I think actually zero or more) “UK establishments”.  
You can of course request the company profile of any of those:

` curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/BR011501`

So it may simply be that you don’t have any company numbers of the form FCnnnnnn that you’re calling this on!

If you are stuck try searching for some well-know international companies (banks, airlines and car firms are good ones) on [https://find-and-update.company-information.service.gov.uk/](https://find-and-update.company-information.service.gov.uk/)

A couple of examples:  
FC004087 - BR001160  
FC003821 - BR011768  
FC003748 - BR003028

Aside - I’m not sure why you’ve needed to wrap the response text in array brackets:

```auto
Set JSON = ParseJson("[" & http.responseText & "]")

```

… the server responds with JSON so unless VB is doing something odd or the ParseJson call is you shouldn’t need that. However I haven’t used these functions in VB myself.

---

<div class="post-metadata">

**Author:** ![carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/c/7bcc69/32.png) [@carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/u/carlos_nunesdesousa)\
**Post date:** [October 7, 2021, 1:52pm UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/4 "2021-10-07T13:52:08Z")

</div>

Hello @voracityemail , many thanks for your reply 🙂  
Your links.uk\_establishments: It is exactly what i am looking for …

I am Looking up FC020086 - BR003764. as an Example

For the First example my code is working  
“[https://api.company-information.service.gov.uk/company/FC022268](https://api.company-information.service.gov.uk/company/FC022268)”…

But for the Second  
“[https://api.company-information.service.gov.uk/company/FC022268/uk-establishments](https://api.company-information.service.gov.uk/company/FC022268/uk-establishments)”…

It does not , so not sure what i am missing …?

The original VB code (Excel Macro) was created from a Ex work colleague, so probably you are right about the ParseJSON…  
I have 2 tabs on the excel one to load the companies names i would like to lookup and other to show the results.  
So I am just try to adapt it to extract the Establishments companies on the same way , and then I will load the results on a SQL table with SSIS .

---

<div class="post-metadata">

**Author:** ![voracityemail](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/v/b77776/32.png) [@voracityemail](http://forum.companieshouse.gov.uk/u/voracityemail)\
**Post date:** [October 7, 2021, 2:42pm UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/5 "2021-10-07T14:42:28Z")

</div>

I can’t debug your code for you but one thing jumps out:

```auto
For Each Item In JSON

```

Again I don’t know what your parser is doing but you will get _different_ JSON structures back from different endpoints in the API. Check [the API documentation](https://developer-specs.company-information.service.gov.uk/companies-house-public-data-api/reference) to see what you’re getting back. It’s your job as a coder to tell your code how to deal with it! The `uk-establishments` endpoint will return a “[resource](https://developer-specs.company-information.service.gov.uk/companies-house-public-data-api/resources/companyukestablishments?v=latest)” (a JSON-encoded _object_) which contains a member called `items`, which is an _array_ and so presumably your can iterate over _that_ with `For Each`. Your code doesn’t appear to be doing that… (If you’re not familiar with JSON I’d quickly read up on that too - very simple but how different systems handle it will have some differences).

If I was in your position I’d first feed the link to the uk-establishment in on the command line and ensure this works and I got back what I thought I was getting. You can [download the curl tool](https://curl.se/download.html) for most operating systems, including [Windows](https://curl.se/windows/) if you don’t have that or something like it already.

After that I’d check in Excel (by dumping the string(s) into a cell - or using a debugger) that the string I was sending was correct and what text I got back in the http response - if I didn’t get anything then I’d check for errors. I’d then check what the output from ParseJson actually was etc.

Finally - not that this is the issue but it’s possible that the variable names aren’t helping you e.g.

> [@carlos\_nunesdesousa](#):
>
> `OrigCompName`

… is not actually the _name_ but the company number (which must be 8 characters but can start with one or two non-numeric characters). Always worth trimming your string to ensure you don’t have leading / trailing spaces…

---

<div class="post-metadata">

**Author:** ![carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/letter_avatar_proxy/v4/letter/c/7bcc69/32.png) [@carlos\_nunesdesousa](http://forum.companieshouse.gov.uk/u/carlos_nunesdesousa)\
**Post date:** [October 7, 2021, 2:57pm UTC](http://forum.companieshouse.gov.uk/t/api-uk-establishment-companies/4437/6 "2021-10-07T14:57:39Z")

</div>

Many thanks, very helpful information

I am going to try Curl tool as I was just reading about it too …

A lot to be learn about API… 🙂