Excel VBA: Fetching domain WHOIS info via API?

Other Questions RapidAPI
1

Hey folks! I’m stuck trying to get WHOIS data for domain names in Excel using VBA and an API. I’ve got domain names in column A and want the WHOIS info in column B. I’ve tried using a rapid API service but can’t get it to work. Here’s what I’ve got so far:

Function FetchDomainInfo(Domain As String)
    Dim apiResponse, apiUrl As String
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")
    apiUrl = "https://example-whois-api.com/v1/lookup"
    httpReq.Open "GET", apiUrl, False
    httpReq.setRequestHeader "auth-token", "your_token_here"
    httpReq.setRequestHeader "Content-Type", "application/json"
    httpReq.Send ("domain=" & Domain)
    apiResponse = httpReq.responseText
    FetchDomainInfo = ParseJsonResponse(apiResponse)
End Function

Sub ProcessDomains()
    Dim cell As Range
    For Each cell In Range("A2:A50")
        If cell.Value <> "" Then
            cell.Offset(0, 1).Value = FetchDomainInfo(cell.Value)
        End If
    Next cell
End Sub

Any ideas what I’m doing wrong or how to fix this? Thanks in advance!

2

I’ve had success with a similar project using the WHOIS API from WhoisXMLAPI. Their documentation is pretty straightforward, and they offer a free tier that’s perfect for testing.

Here’s a tip from my experience: Make sure you’re properly URL encoding the domain parameter in your API call. I ran into issues initially because special characters weren’t being handled correctly.

Also, consider implementing error handling and rate limiting in your code. WHOIS APIs often have request limits, and you don’t want to hit those mid-process.

Lastly, if you’re dealing with a large number of domains, you might want to look into batch processing capabilities. Some APIs allow you to submit multiple domains in a single request, which can significantly speed up your data collection.

Hope this helps point you in the right direction!

3

I’ve tackled similar challenges using the WhoisXMLAPI service. Their RESTful API is quite robust and easy to integrate with VBA. One key point to consider is proper error handling. WHOIS lookups can sometimes fail or return unexpected results, so it’s crucial to implement try-catch blocks or equivalent error handling mechanisms in your VBA code.

Another aspect to keep in mind is caching. If you’re querying the same domains frequently, consider storing the results locally to reduce API calls and improve performance. You could use a separate worksheet or even a lightweight database for this purpose.

Lastly, ensure you’re parsing the JSON response correctly. The VBA-JSON library by Tim Hall is excellent for this task and can simplify the process of extracting the relevant information from the API response.

4

hey dave, i’ve used whoisfreaks api for this kinda thing. pretty easy to work with. make sure ur sending the domain as a query parameter, not in the body. like this:

apiUrl = “https://api.whoisfreaks.com/v1.0/whois?apiKey=YOUR_API_KEY&domainName=” & Domain

also, check ur ParseJsonResponse function. might be the issue there.