Excel VBA API Interaction - Domai

[kirbyfez]

Howdy,

I have been assigned to create a macro which will check available domains via a website called domai.nr

They do have a API as well available at: https://www.mashape.com/nbio/domainr#!documentation

I was able to get the availability of the domain by using a code to open the site and use the getelementsbyID.innertext

But the issue is that it's extremely slow and my client is expecting something far better, integrating with the API as that would be a lot faster.

Can someone give me a hand with this? I am guessing I need to use a get function and so on but I really just can't figure it out.

Been trying to find tutorials or similar projects but just can't seem to figure it out.

If someone is comfortable with this, then I can send a sample worksheet which does another API integrated domain checker thing.

I can provide more information as well if required.

Hoping someone can help me as I really don't want to tell my client that I can't complete the tool he requires.

Best regards
Kirbs

 

[wigi]

Kirbs

I am not going to be of help for you on this one, mainly because of 2 reasons:

- this will require a lot of investment in time and knowledge, which I cannot associate with an unpaid public forum like this one. If you can't find tutorials, how should someone else here find them?
- I find it immoral to charge a client money for things you can't do yourself. How will you provide help and assistance later on, in case something unexpected arrives or an extra requirement or ... ?

This being said, I sincerely hope that someone else can be of assistance. (My post will help to bring the topic up a little bit.)

Wigi

 

[kirbyfez]

Hey,

Was hoping here someone already knew it and wouldnt have to research it.

Reason I don't want to let it go now is because the file is a part of a bigger project where I've already but a lot of time into it and now I've hit a snag I can't seem to be able to solve.

No idea if this will help but I have the following snipits with is for the connection, just have no idea how to use them in excel


.NET
HttpResponse<MyClass> response = Unirest.get("https://domainr-domainr.p.mashape.com/json/info?q=domai.nr")
<code data-language="net" class="snippet-example rainbow" data-json="<span class="support tag open">{</span> <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"query"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain_idna"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>">"host"</span>: <span class="string value">""</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"subdomain"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>">"path"</span>: <span class="string value">""</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, taken">"availability"</span>: <span class="string value">"taken"</span>, <span class="support attribute">"tld"</span>: <span class="support tag open">{</span> <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain"</span>: <span class="string value">"nr"</span>, <span class="support attribute">"wikipedia_url"</span>: <span class="string value">"http://domai.nr/nr/wikipedia"</span>, <span class="support attribute">"iana_url"</span>: <span class="string value">"http://domai.nr/nr/iana"</span> <span class="support tag close">}</span>, <span class="support attribute">"subdomains"</span>: <span class="support tag open">[</span><span class="support tag close">]</span>, <span class="support attribute documented-field" data-desc-0="<strong>Boolean</strong>, true">"subregistration_permitted"</span>: <span class="constant language">true</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/www">"www_url"</span>: <span class="string value">"http://domai.nr/domai.nr/www"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/whois">"whois_url"</span>: <span class="string value">"http://domai.nr/domai.nr/whois"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register"</span>, <span class="support attribute">"registrars"</span>: <span class="support tag open">[</span> <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"101domain.com"</span>, <span class="support attribute">"name"</span>: <span class="string value">"101domain"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/101domain.com"</span> <span class="support tag close">}</span>, <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"marcaria.com"</span>, <span class="support attribute">"name"</span>: <span class="string value">"Marcaria"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/marcaria.com"</span> <span class="support tag close">}</span>, <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"cenpac.net.nr"</span>, <span class="support attribute">"name"</span>: <span class="string value">"CenpacNET"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/cenpac.net.nr"</span> <span class="support tag close">}</span> <span class="support tag close">]</span><span class="support tag close">}</span>" style="margin: 0px; padding: 0px; border: 0px; box-sizing: border-box; font-family: Consolas, 'Liberation Mono', Courier, monospace; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; display: inline; overflow: hidden;"> .header("X-Mashape-Authorization", "MY SECURITY CODE") .asJson<MyClass>();JAVA
<code data-language="java" class="snippet-example rainbow" data-json="<span class="support tag open">{</span> <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"query"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain_idna"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>">"host"</span>: <span class="string value">""</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"subdomain"</span>: <span class="string value">"domai.nr"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>">"path"</span>: <span class="string value">""</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, taken">"availability"</span>: <span class="string value">"taken"</span>, <span class="support attribute">"tld"</span>: <span class="support tag open">{</span> <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, domai.nr">"domain"</span>: <span class="string value">"nr"</span>, <span class="support attribute">"wikipedia_url"</span>: <span class="string value">"http://domai.nr/nr/wikipedia"</span>, <span class="support attribute">"iana_url"</span>: <span class="string value">"http://domai.nr/nr/iana"</span> <span class="support tag close">}</span>, <span class="support attribute">"subdomains"</span>: <span class="support tag open">[</span><span class="support tag close">]</span>, <span class="support attribute documented-field" data-desc-0="<strong>Boolean</strong>, true">"subregistration_permitted"</span>: <span class="constant language">true</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/www">"www_url"</span>: <span class="string value">"http://domai.nr/domai.nr/www"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/whois">"whois_url"</span>: <span class="string value">"http://domai.nr/domai.nr/whois"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register"</span>, <span class="support attribute">"registrars"</span>: <span class="support tag open">[</span> <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"101domain.com"</span>, <span class="support attribute">"name"</span>: <span class="string value">"101domain"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/101domain.com"</span> <span class="support tag close">}</span>, <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"marcaria.com"</span>, <span class="support attribute">"name"</span>: <span class="string value">"Marcaria"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/marcaria.com"</span> <span class="support tag close">}</span>, <span class="support tag open">{</span> <span class="support attribute">"registrar"</span>: <span class="string value">"cenpac.net.nr"</span>, <span class="support attribute">"name"</span>: <span class="string value">"CenpacNET"</span>, <span class="support attribute documented-field" data-desc-0="<strong>String</strong>, http://domai.nr/domai.nr/register">"register_url"</span>: <span class="string value">"http://domai.nr/domai.nr/register/cenpac.net.nr"</span> <span class="support tag close">}</span> <span class="support tag close">]</span><span class="support tag close">}</span>" style="margin: 0px; padding: 0px; border: 0px; box-sizing: border-box; font-family: Consolas, 'Liberation Mono', Courier, monospace; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; display: inline; overflow: hidden;">HttpResponse<JsonNode> request = Unirest.get("https://domainr-domainr.p.mashape.com/json/info?q=domai.nr") .header("X-Mashape-Authorization", "KL1WPt2Fww0AFIEuNCEE6UMvkhD3E897") .asJson();</code></pre></code></pre>
Best regards
Christian

 

[kirbyfez]

One step closer, I've been able to connect and get the response the next one I'm sure is easy, i'm just retarded.

I get this very long response. All i want actually is "available" which is after availability in the string below.

was thinking about using a mid function connected with a find or something like that then mixed with a left, but i'm sure there is a easier way.

Anyone have any idea?

Best regards
Christian

[TABLE="width: 2045"]
<tbody>[TR]
[TD="width: 2045"]{"query":"fdsafdfdas.com","domain":"fdsafdfdas.com","domain_idna":"fdsafdfdas.com","host":"","subdomain":"fdsafdfdas.com","path":"","availability":"available","tld":{"domain":"com","wikipedia_url":"https://domai.nr/com/wikipedia","iana_url":"https://domai.nr/com/iana"},"subdomains":[],"subregistration_permitted":true,"www_url":"https://domai.nr/fdsafdfdas.com/www","whois_url":"https://domai.nr/fdsafdfdas.com/whois","register_url":"https://domai.nr/fdsafdfdas.com/register","registrars":[{"registrar":"iwantmyname.com","name":"iWantMyName","register_url":"https://domai.nr/fdsafdfdas.com/register/iwantmyname.com"},{"registrar":"europeregistry.com","name":"Europe Registry","register_url":"https://domai.nr/fdsafdfdas.com/register/europeregistry.com"},{"registrar":"uniteddomains.com","name":"United Domains","register_url":"https://domai.nr/fdsafdfdas.com/register/uniteddomains.com"},{"registrar":"101domain.com","name":"101domain","register_url":"https://domai.nr/fdsafdfdas.com/register/101domain.com"},{"registrar":"marcaria.com","name":"Marcaria","register_url":"https://domai.nr/fdsafdfdas.com/register/marcaria.com"},{"registrar":"godaddy.com","name":"Go Daddy","register_url":"https://domai.nr/fdsafdfdas.com/register/godaddy.com"},{"registrar":"instra.com","name":"Instra","register_url":"https://domai.nr/fdsafdfdas.com/register/instra.com"},{"registrar":"name.com","name":"Name.com","register_url":"https://domai.nr/fdsafdfdas.com/register/name.com"},{"registrar":"onlydomains.com","name":"Only Domains","register_url":"https://domai.nr/fdsafdfdas.com/register/onlydomains.com"},{"registrar":"dreamhost.com","name":"Dreamhost","register_url":"https://domai.nr/fdsafdfdas.com/register/dreamhost.com"},{"registrar":"nameterrific.com","name":"NameTerrific","register_url":"https://domai.nr/fdsafdfdas.com/register/nameterrific.com"},{"registrar":"namecheap.com","name":"Namecheap","register_url":"https://domai.nr/fdsafdfdas.com/register/namecheap.com"},{"registrar":"dyn.com","name":"Dyn","register_url":"https://domai.nr/fdsafdfdas.com/register/dyn.com"},{"registrar":"onepagerapp.com","name":"Onepager","register_url":"https://domai.nr/fdsafdfdas.com/register/onepagerapp.com"},{"registrar":"hover.com","name":"Hover","register_url":"https://domai.nr/fdsafdfdas.com/register/hover.com"},{"registrar":"dnsimple.com","name":"DNSimple","register_url":"https://domai.nr/fdsafdfdas.com/register/dnsimple.com"},{"registrar":"eurodns.com","name":"EuroDNS","register_url":"https://domai.nr/fdsafdfdas.com/register/eurodns.com"},{"registrar":"sedo.com","name":"Sedo","register_url":"https://domai.nr/fdsafdfdas.com/register/sedo.com"},{"registrar":"gandi.net","name":"Gandi","register_url":"https://domai.nr/fdsafdfdas.com/register/gandi.net"}]}[/TD]
[/TR]
</tbody>[/TABLE]

 

[wigi]

Hello

Smaller help, I will offer...

With that text above in cell A1, this oneliner will work:

MsgBox Split(Filter(Split([A1].Text, ","), """availability""")(0), ":")(1)

 

[kirbyfez]

Thanks wigi! been a huge help.