MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12345Next
Return Post new threads
Author: 19marc87

UK Companies House API Code

[Copy link]

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 10:05:52 | All posts



},
  accounts: {
    next_made_up_to: 2016-04-05,




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 11:05:34 | All posts



do you want that instead of the last accounts made up to?




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 11:41:38 | All posts



Yes please, i can add in the code




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 12:03:08 | All posts



i mean if you can post it on here plz




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 13:24:05 | All posts




Public

Sub

getData
()
   
   
Dim
cll     
As
Range
   
Dim
api     
As
CompaniesHouseAPI
   
Dim
company
As
CHCompany
   
   
Set
api =
New
CompaniesHouseAPI
      
   
    api.companiesHouseBaseUrl = Constants.BASE_URL_API
    api.companiesHouseApiKey = Constants.API_KEY
   
   
For

Each
cll In
Sheets
(
population
).
Cells
(1, 1).CurrentRegion.
Resize
(, 1).
Offset
(1, 1)
        
If

Len
(cll.Value2)  0
Then
            On
Error
Resume
Next
            
Set
company = api.
companyById
(cll.Value2)
            cll.
Offset
(0, 2).Value = company.accounts.next_made_up_to
            cll.
Offset
(0, 3).Value = company.accounts.next_due
            cll.
Offset
(0, 4).Value = company.confirmation_statement.next_due
            cll.
Offset
(0, 5).Value =
formatAddress
(company.registered_office_address)
        
End

If
   
Next
cll
  
   
End

Sub





Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 15:00:19 | All posts



this is fantastic, thank you.
i was wondering whether you could us an API code to push information to an accountancy book keeping cloud based software?




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 16:35:10 | All posts



Yes, if the accountancy cloud software has a published api that allows it.




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 17:42:10 | All posts



Hi Kyle
Yes it does allow it, it has a separate API per client.
Can you help me in adding an additional column on the spreadsheet for pulling the company registered number
Thanks
marc
Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-4-2016 09:44:42 | All posts



Hi Kyle
Thank you for your reply
I have attached a spreadsheet, i hope its not too rubbish.
I have 3 sheets, first 1 is what it would look like before the code initialized, i would like to drop the the information into the spreadsheet on the second sheet.
Then 3rd sheet is the results, but its would be for more multiple such as 300 or more.
The purpose is to be able to look up data to Companies House website on a mass basis and identify deadlines and any discrepancies.
Thanks
Marc








  • Co House API Example.xlsx
    (10.3 KB, 12 views)
    Download








  • Reply Support Opposition

    Props Report

    3

    Topics

    151

    Posts

    377

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    377
    Post on 10-4-2016 15:54:03 | All posts



    @AB33 I'd do it and wipe my machine if it's a problem. Re-installing windows is pretty trivial, especially with win 10, I can do a clean build and have all my applications installed and updated in under a day (most of it is just clicking next).
    @Marc, you want something like the attached. I've also included the pertinent code below:
    Option Explicit


    Private

    Sub

    getData
    ()
       
       
    Dim
    cll     
    As
    Range
       
    Dim
    api     
    As
    CompaniesHouseAPI
       
    Dim
    company
    As
    CHCompany
       
       
    Set
    api =
    New
    CompaniesHouseAPI
          
       
        api.companiesHouseBaseUrl = Constants.BASE_URL_API
        api.companiesHouseApiKey = Constants.API_KEY
       
       
    For

    Each
    cll In
    Sheets
    (
    population
    ).
    Cells
    (1, 1).CurrentRegion.
    Resize
    (, 1).
    Offset
    (1, 1)
            
    If

    Len
    (cll.Value2)  0
    Then
                
    Set
    company = api.
    companyById
    (cll.Value2)
                cll.
    Offset
    (0, 1).Value = company.accounts.last_accounts.made_up_to
                cll.
    Offset
    (0, 2).Value = company.accounts.next_due
                cll.
    Offset
    (0, 3).Value = company.confirmation_statement.next_due
                cll.
    Offset
    (0, 4).Value =
    formatAddress
    (company.registered_office_address)
            
    End

    If
                
       
    Next
    cll
      
       
    End

    Sub

    Private

    Function

    formatAddress
    (address
    As
    CHAddress)
    As

    String
       
            
    formatAddress
    = address.address_line_1
            
    formatAddress
    =
    formatAddress
      

      address.address_line_2
            
    formatAddress
    =
    formatAddress
      

      address.locality
            
    formatAddress
    =
    formatAddress
      

      address.region
            
    formatAddress
    =
    formatAddress
      

      address.postal_code
            
    formatAddress
    =
    formatAddress
      

      address.country
       
       
    formatAddress
    = Application.
    Trim
    (
    formatAddress
    )
       
    End

    Function
    You will obviously need to get your own API key (and insert it into the CONSTANTS module). In addition, the rate is throttled, so expect this to stop working if you run  600 queries every five minutes.








  • CompaniesHouseClient Sample.xlsb
    (75.5 KB, 13 views)
    Download








  • Reply Support Opposition

    Props Report

    12345Next
    Return Post new threads

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 2-21-2018 02:24 , Processed in 0.230543 second(s), 17 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!