Companies House Officer data

Hey guys,

New here (hoping to be very active as time goes on!) but hope you don't mind my first post being a request for assistance or guidance.

I found a very useful file, created by Kyle123 and have adapted it to my needs (somewhat) in one respect. However I am now trying to use it to find Officer data, but keep running in to problems.

The error I keep getting is 'Compile error: Method or data member not found'.

Does anybody have any idea what could be causing this error? I have attached the file for reference (though have removed my Companies House API) with the code that isn't working in Module Two.

Any help will be greatly appreciated.

Thank you and kind regards,

Ben

Stepping through the macro code reveals the following:

Please Login or Register  to view this content.

Hi Logit, thank you for your reply.

Any particular reason that the API is highlighted? I have a working API key in the workbook on my machine and it works absolutely fine with the filing data, however I just cannot get the officer data to work - presumably because of the 'Object variable...' errors that you have pointed out above.

Do you have any idea why this is happening? It could well be that the 'officer.' is the wrong prefix, however I have tried multiple solutions but to no avail.

Thank you again,

Ben

Thanks AB33, you're right, however it may not help you Ben. The officers collection has what you're after, but there can be more than one officer which wouldn't fit your structure (presumably), so you could return only the first officer, or loop through them all and save their details elsewhere:

First officer (index may be one based, not zero, I can't remember without looking):

Please Login or Register  to view this content.

All officers:

Please Login or Register  to view this content.

@Logit, what are you doing to get a syntax error?

They're not in the site page, they're from the API documentation - https://developer.companieshouse.gov...-resource.html

Hi Kyle,
Thanks mate!

As it would happen, I actually have a version of this with inheritance (interface inheritance anyway) as I had slightly different implementations for 64 bit Excel (as it stands the workbook doesn't work in 64-bit) - I've now mostly resolved these to make the code compatible with 64 and 32 bit.

Hi Kyle,
Would you make it public at some point?

Here's a version with the interface implementation, it's probably non working (might if you're luck though!), but feel free to pick through it.

You'll need an API key (they're free) and put it in the constants module.

This is a somewhat more involved version of the initial spreadsheet as it deals with the rate throttling - it includes a timeout.

The workbook allows searching of companies, lets you select the one you want and then populates the additional information

It has a different implementation of the JSON parsing, one for 32 bit and one for 64 bit, both CompaniesHouseResponseParser32 and CompaniesHouseResponseParser64 both inherit the ICHJSONParser interface

Hi Kyle,
Thanks again!
I will play with it and see how far I can go.

You want:

Please Login or Register  to view this content.

Kyle,

I think I love you.

Cheers mate!

Out of interest Kyle, have you tested your API work with fetching the filings at Companies House? For example, using the template that I uploaded for the 'Officers' layout, would it be possible to have your worksheet list all filings within the last, say, 24 months, with links directly to that document? Interested to see the capabilities of what you've created - it's awesome.