Using Excel and VBA to get API/Web Data with Multiple Queries

[cfc06]

Hello all,

This is my first post and I would be grateful if you could help me . I have spreadsheets with a large amount of data for querying. I am looking for each cell of data to be searched through API/Web Query and then resultant from that specific data to be extracted to Excel. What is the best way to go about searching multiple queries so to speak? Would it have to be combined with a macro which searches one cell after another?

I appreciate your help.

I have attached a sample file along with the URL which needs to be queried.

Thank you!

[AB33]

The API URL does not work, but companies house does.
What sort of data do you wish to extract, is it just the name of the director or all his/her details?

[cfc06]

Thanks for the reply. Initially I was just looking for the name of the director, as I have a file to VLOOKUP the addresses.

[AB33]

It works on the active sheet.
Should have valid nos on column A and result will be on column B.

[Kyle123]

If you're after something a bit more comprehensive, I made a VBA wrapper around the companies house API here: http://www.excelforum.com/tips-and-t...house-api.html it is in beta though so don't blame me if it breaks!

[AB33]

Hi Kyle,
Glad you are in this thread.
I know Classname does not work with late binding, but what I am not sure is, even with early binding, it only works with post method. Is this right? I am unable to get the text from H2 using a tagname, so I had to loop through each H2.
What I am picking up is, there are no "hard and fast" rules with scrapping a web. It is trial and error.
I will have a look at your API.

[Kyle123]

Hi, no it works with post or get, the dom is completely unrelated to the request. There shouldn't be an issue with tagname, I'll have a look at your code when I'm in front of a computer

[AB33]

Hi Kyle,
I managed to avoid a loop, but could easily error if the H2 is changed.

[cfc06]

Hi Kyle, thanks for linking me to this. I am a bit unfamiliar with how to use a VBA wrapper. Do I need to enter the functions into cells as formula to obtain the data?

I also added positive reputation for you and AB33 for your help.

[Kyle123]

Hi Kyle,
I managed to avoid a loop, but could easily error if the H2 is changed.

That's why it's better to use the api rather than web scraping. The api won't change, they're published to a specification