Implementing WhoIs Lookup in Excel VBA: Domain Info Retrieval Challenge

Other Questions RapidAPI
1

Hey Excel gurus! I’m pulling my hair out trying to get domain info into my spreadsheet. I’ve got a list of domains in column A and want to fetch their WhoIs details into column B using VBA and an API. I’ve played around with some code but it’s not cooperating. Any Excel wizards out there who can point me in the right direction?

Function FetchDomainInfo(DomainStr As String)
    Dim apiResponse, apiUrl As String
    Dim httpObj As Object
    Set httpObj = CreateObject("MSXML2.ServerXMLHTTP")
    apiUrl = "https://example-api.com/whois/"
    httpObj.Open "GET", apiUrl, False
    httpObj.setRequestHeader "api-key", "your-api-key-here"
    httpObj.Send "domain=" & DomainStr
    apiResponse = httpObj.responseText
    FetchDomainInfo = ParseJsonResponse(apiResponse)
End Function

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

This code is just a starting point. It’s not working as expected. Any ideas on how to fix it or a better approach? Thanks in advance!

2

I’ve implemented similar functionality in Excel VBA before. One crucial aspect you might want to consider is error handling. Network issues or API hiccups can throw a wrench in your code, so wrapping your API call in error handling routines is essential.

Parsing JSON in VBA can be cumbersome; I recommend using a third-party library like VBA-JSON to simplify the process when processing API responses.

Additionally, consider caching results to minimize repetitive API calls. This is particularly useful for large datasets and when you might be querying the same domain multiple times. Finally, remember to adhere to the API’s rate limits and terms of service. Good luck with your project!

3

hey dancingfox, ive used whois apis before and ur on the right track. couple things to check:

  1. make sure ur api key is valid
  2. double check the api endpoint url
  3. try printing the api response to debug

also, might wanna add error handling for failed requests. lmk if u need more help!

4

I’ve tackled this exact problem before, and I can tell you it’s a bit tricky. Your approach is solid, but there are a few tweaks that might help. First, ensure you’re using the correct HTTP method for the API - some require POST instead of GET. Also, you might need to URL encode the domain parameter.

Here’s a snippet that worked for me:

apiUrl = apiUrl & "?domain=" & WorksheetFunction.EncodeURL(DomainStr)
httpObj.Open "GET", apiUrl, False
httpObj.Send

Another tip: Some APIs return rate limits. You might want to add a small delay between requests to avoid getting blocked. Something like:

Application.Wait Now + TimeValue("00:00:01")

Hope this helps! Let me know if you run into any other issues.