How can I integrate RapidAPI with VBA in Excel for WhoIs queries?

Other Questions RapidAPI
1

I have come across several discussions on this topic, but I'm still struggling to find a comprehensive solution. I would really appreciate any assistance on this matter.

Currently, I'm trying to utilize RapidAPI in my Excel VBA script, but I can't seem to get it to function properly. My goal is to obtain WhoIs details for domain names listed in column A, and I'd like the API responses to be populated in column B. Could someone please provide some guidance?

Function FetchWhoIsInfo(Domain As String)     Dim responseData, requestUrl, requestParams As String     Dim jsonResponse, httpObject, resultString, parsedJson As Object     Set httpObject = CreateObject("WinHttp.WinHttpRequest.5.1")     requestUrl = "https://jsonwhois.p.rapidapi.com/api/v1/whois/"     httpObject.Open "GET", requestUrl, False     httpObject.setRequestHeader "authorization", "Token token=your_token_here"     httpObject.setRequestHeader "x-rapidapi-host", "jsonwhois.p.rapidapi.com"     httpObject.setRequestHeader "x-rapidapi-key", "your_api_key_here"     requestParams = "domain=" + Domain     httpObject.Send (requestParams)     resultString = httpObject.responseText     responseData = resultString     Set parsedJson = JsonConverter.ParseJson(responseData)     FetchWhoIsInfo = parsedJson End Function

Sub CollectWhoIsData()     Dim domainName As String     For Each domainName In Range(“A2:A50”)         If Not IsEmpty(domainName.Value) Then             Range(“B” & Rows.Count).End(xlUp).Offset(1, 0) = FetchWhoIsInfo(domainName.Value)         End If     Next domainName End Sub

2

To successfully integrate RapidAPI with VBA for retrieving WhoIs information, you can follow these steps for a streamlined process:

  1. Ensure you have set up the JSON parsing library in VBA: VBA natively does not parse JSON. Consider using a library like VBA-JSON by Tim Hall. Save the downloaded JSON.bas file and import it into your VBA editor.

  2. Update your function to include error handling: This ensures the program can handle issues gracefully, whether from a bad response or an incorrect URL.

  3. Ensure the correct RapidAPI endpoint is being used: Double-check your API endpoint and that your request headers use the correct API keys. This is essential for authentication.

Function FetchWhoIsInfo(Domain As String)  
    Dim responseData As String
    Dim requestUrl As String
    Dim jsonResponse As Object
    Dim httpObject As Object
    
    On Error GoTo ErrorHandler
    Set httpObject = CreateObject("WinHttp.WinHttpRequest.5.1")
    requestUrl = "https://jsonwhois.p.rapidapi.com/api/v1/whois?domain=" & Domain
    
    httpObject.Open "GET", requestUrl, False
    httpObject.setRequestHeader "x-rapidapi-host", "jsonwhois.p.rapidapi.com"
    httpObject.setRequestHeader "x-rapidapi-key", "your_api_key_here"
    
    httpObject.Send 
    responseData = httpObject.responseText
    
    Set jsonResponse = JsonConverter.ParseJson(responseData)
    FetchWhoIsInfo = jsonResponse("whoisRecord")
    Exit Function

ErrorHandler:
    FetchWhoIsInfo = "Error: " & Err.Description
End Function

Sub CollectWhoIsData()
    Dim cell As Range
    For Each cell In Range("A2:A50")
        If Not IsEmpty(cell.Value) Then
            cell.Offset(0, 1).Value = FetchWhoIsInfo(cell.Value)
        End If
    Next cell
End Sub

Make sure to replace your_api_key_here with your actual RapidAPI key. This code also directs the data to the adjacent column efficiently. Let me know if you need further clarifications!