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 01:08:51 | All posts



Thank you Kyle, i am such a divy haha
it works great, however, i have listed 280 clients and i get the following error after line 79 Object variable or with block variable not set
Also, majority of the Year End Accounts Date is only bringing through 01/01/1990 instead of the date
Thanks
Marc




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 02:34:59 | All posts



Could you post a workbook with the offending company numbers in?




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

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



Sorry but i wont be able to because of data protection.
Everything pulls through apart from the majority of the Year End Dates




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 03:36:34 | All posts



Why data protection? It's public information. I can't help you if you can't give me an example




Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 04:08:27 | All posts



here is an example of the clients
2015 Solutions Ltd09955615
4gk Consultants Ltd09928394
AG1 Consultancy Limited10055196




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 05:39:43 | All posts



It's because they don't have any last accounts posted - well they're not accessible on CH anyway
To stop the error, replace the part of the code in the module with this:
   
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, 1).Value = company.company_status
            cll.
Offset
(0, 2).Value = company.accounts.last_accounts.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(Notice the On Error Resume Next)










Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

Integral
94
 Author| Post on 10-5-2016 06:59:48 | All posts



ok i understand now, is there a way to but in the code if it is the first year then put 'first accounts made up to'
you have been great by the way




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 10-5-2016 07:49:33 | All posts



Sure, try this:
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, 1).Value =
IIf
(company.accounts.last_accounts.account_type =
null
,
first accounts made up to
, 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
Actually, the above just checks if there are any last accounts, if not then it puts 'First accounts made up to', would you rather check the date of creation?










Reply Support Opposition

Props Report

2

Topics

43

Posts

94

Integral

Member

Rank: 2

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



Is there a way to pull the actual date of the first accounts date, i have inputted the code and its the wording that comes up




Reply Support Opposition

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

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



Which is the first accounts date in the below:
{
  
etag
:
a22e8133613cea50ecb699362543ff67faef69e8
,
  
company_status
:
active
,
  
has_insolvency_history
: false,
  
has_charges
: false,
  
jurisdiction
:
england-wales
,
  
company_number
:
09928394
,
  
date_of_creation
:
2015-12-23
,
  
annual_return
: {},
  
company_name
:
4GK CONSULTANTS LTD
,
  
registered_office_address
: {
   
country
:
United Kingdom
,
   
locality
:
Dalton-In-Furness
,
   
postal_code
:
LA15 8AA
,
   
region
:
Cumbria
,
   
address_line_1
:
Market Street House
,
   
address_line_2
:
72 Market Street
  },
  
accounts
: {
   
next_made_up_to
:
2016-04-05
,
   
overdue
: false,
   
last_accounts
: {
      
type
:
null
    },
   
accounting_reference_date
: {
      
day
:
05
,
      
month
:
04
    },
   
next_due
:
2017-01-05
  },
  
undeliverable_registered_office_address
: false,
  
type
:
ltd
,
  
confirmation_statement
: {
   
next_made_up_to
:
2016-12-22
,
   
next_due
:
2017-01-05
  },
  
links
: {
   
self
:
/company/09928394
,
   
filing_history
:
/company/09928394/filing-history
,
   
officers
:
/company/09928394/officers
  },
  
can_file
: true
}




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:25 , Processed in 0.388312 second(s), 17 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!