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
View: 1339|Reply: 43

UK Companies House API Code

[Copy link]

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
Post on 10-4-2016 05:17:02 | All posts |Read mode
Hello
I was reviewing the forum for a solution to my thought and came accross the thread 'UK Companies House API' a chap named Kyle123 developed a spreadsheet which linked up to companies house via a spreadsheet and pulled particular information.
I am learning on excel and trying to learn code.
I am unfortunately too much of a novice to understand the code on the spreadsheet.
What i am trying to achieve is a link to Companies house via an API which would pull the following data from Companies house based up a the 'company registration number'
I would like to see the following results 'Next Accounts', 'Next Accounts Due By', 'Confirmation Due by', Registered Office'
Here is an example of the spreadsheet i would like to show:
Company NameCompany Number Next Accounts Next Accounts Due ByConfirmation Statement Due ByRegistered Office Addres
A Limited         1234567            (Date)              (Date)                              (Date)
B Limited          1234567            (Date)              (Date)                              (Date)
C Limited         1234567            (Date)              (Date)                              (Date)
D Limited         1234567            (Date)              (Date)                              (Date)

Thank you
Marc




Reply

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-4-2016 06:14:42 | All posts



Please upload a workbook, not copy and paste into the thread, it makes it easier to help you since no-one has to re-create your workbook.
Attach a sample workbook.  Make sure there is just enough data to demonstrate your need.  Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.  Make sure your desired results are shown, mock them up manually if necessary.  
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to
[color=] Manage Attachments
to open the upload window.
Please also include some actual company registration numbers so someone doesn't have to go searching for some to help.
Finally, my mentioned thread is here if anyone is looking to help: http://www.excelbar.com




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-4-2016 07:20:13 | 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, 10 views)
    Download








  • Reply Support Opposition

    Props Report

    6

    Topics

    732

    Posts

    1620

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1620
    Post on 10-4-2016 08:50:09 | All posts



    Hi Kyle,
    I have not be able to test your work, as I still have got problem with this bloody error 429. I know there are lots of things missing from my registrar. Active X component is one of them. I do not want to miss up my pc by manually editing the window registrar. MS has asked me to fork out $500 dollar to fix this issue. I am really very frustrated with this error, as I am unable to create an object, even using late binding. I have spent over the last 6 months searching for on-line solution, but not luck yet.
    By the way, could your code download the pdf accounts from companies house?










    Reply Support Opposition

    Props Report

    6

    Topics

    732

    Posts

    1620

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1620
    Post on 10-4-2016 10:28:58 | All posts



    Hi Kyle,
    I have not be able to test your work, as I still have got problem with this bloody error 429. I know there are lots of things missing from my registrar. Active X component is one of them. I do not want to miss up my pc by manually editing the window registrar. MS has asked me to fork out $500 dollar to fix this issue. I am really very frustrated with this error, as I am unable to create an object, even using late binding. I have spent over the last 6 months searching for on-line solution, but not luck yet.
    By the way, could your code download the pdf accounts from companies house?










    Reply Support Opposition

    Props Report

    3

    Topics

    151

    Posts

    377

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    377
    Post on 10-4-2016 10:46:36 | All posts



    @op I'll hAve a look at this in the morning for you
    @ab33 can't you just stick the Windows disk in and reboot? I tend to do that about every 6 months or so. My code doesn't do that now but it would be pretty easy to add that if needed, I only mapped the data I needed at the time




    Reply Support Opposition

    Props Report

    6

    Topics

    732

    Posts

    1620

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1620
    Post on 10-4-2016 11:02:29 | All posts



    Hi Kyle,
    I had the same issue with windows 7. My other  windows XP PC still shows the same error. In fact, I had re-installed windows 7 before I upgraded to windows 10. Trust me, it might be easier to fix this error for some one  like you who is computer savvy. I have no idea what is mapping.
    This is very common error  and the help from MS is the same crap-install and re-install MS office. After lots of search, I have pinned down the error to missing some thing from the registrar.
    This website has good info.
    http://mkksharepoint.blogspot.co.uk/...component.html
    but can take the chance to destroy my PC.




    Reply Support Opposition

    Props Report

    3

    Topics

    151

    Posts

    377

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    377
    Post on 10-4-2016 12:03:01 | 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, 11 views)
    Download








  • Reply Support Opposition

    Props Report

    6

    Topics

    732

    Posts

    1620

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1620
    Post on 10-4-2016 13:34:53 | All posts



    Hi Kyle,
    I thought wipe out is over kill. I do not know how I re-install all applications. I am also not sure if these steps could be the remedy for the issue I am having. I had the same problem with Windows 7. So, I would have expected windows 10 to add any missing from registrar, but did not.










    Reply Support Opposition

    Props Report

    3

    Topics

    151

    Posts

    377

    Integral

    Conqueror

    Rank: 3Rank: 3

    Integral
    377
    Post on 10-4-2016 13:41:30 | All posts



    I wipe every six months

    does wonders for performance




    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-22-2018 23:02 , Processed in 0.305773 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!