Need Help to retrieve an API result in Worksheet

[Atri R]

Hello,

I am designing an excel file which will provide me the company website URL taking the Company name as Input. For this I have found out a free API which does this exact Name->Domain mapping.

The API goes like this:

https://autocomplete.clearbit.com/v1/companies/suggest?query=[C_NAME]

where C_NAME is the company names present in the worksheet column.

• The response of the API usually has 3 parameters (name, domain and logo). I would like to have the domain value only to be inserted in the excel column.
• At times, the response can be multiple sub nodes - in that case I would love to have the first domain only(which is possibly most relevant).
• Also, sometimes the response might be empty when the API is not able to lookup for the company name. In this case, the cell can be populated with a default value.

Can anyone help me write a macro/VBA code for this small function? It would be of a great help.

TIA

 

[Atri R]

Now you've got me, I'm not at a computer right now so can't recall where it is on a PC never mind a Mac.

I'll check it out and get back to you.

PS Does the workbook actually work for you?

What happens if you add more company names to the table on the first sheet and then right click the table on the second sheet and select Refresh?

It throws up an error

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'}</style>

The data connection can't be refreshed, as this type of data connection is not supported in this version of Excel.

 

[Norie]

Oops, had a look and Power Query/Get and transform aren't supported on Excel for Mac.

Do you know if you can use VBA on your version of Excel?

 

[Atri R]

Oops, had a look and Power Query/Get and transform aren't supported on Excel for Mac.

Do you know if you can use VBA on your version of Excel?

Yes I can. And that would be very useful Thanks a lot!