I’m struggling to get my Excel VBA code working with RapidAPI for domain lookups. I’ve searched through other posts but haven’t found a clear solution that works.
I’m trying to build a function that reads domain names from column A in my Excel sheet and fetches their registration details using RapidAPI. The results should populate in column B. My current code doesn’t seem to work properly and I’m not sure what I’m missing.
Here’s what I have so far:
Function FetchDomainInfo(siteName As String)
Dim responseData, apiUrl, queryParams As String
Dim httpRequest, resultData, parsedJson As Object
Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
apiUrl = "https://jsonwhois.p.rapidapi.com/api/v1/whois/"
httpRequest.Open "GET", apiUrl, False
httpRequest.setRequestHeader "authorization", "Token token=your_token_here"
httpRequest.setRequestHeader "x-rapidapi-host", "jsonwhois.p.rapidapi.com"
httpRequest.setRequestHeader "x-rapidapi-key", "your_api_key_here"
queryParams = "domain=" + siteName
httpRequest.Send (queryParams)
resultData = httpRequest.responseText
responseData = resultData
Set parsedJson = JsonConverter.ParseJson(responseData)
FetchDomainInfo parsedJson
End Function
Sub ProcessDomains()
Dim cellValue As String
For Each cellValue In Range("A2:A50")
If Not IsEmpty(cellValue.Value) Then
Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = FetchDomainInfo(cellValue.Value)
Next cellValue
End Sub
Can anyone help me figure out what’s wrong with this approach? Any working examples would be really helpful.
just noticed your authorization header looks wrong for rapidapi - should only use the x-rapidapi-key header, not both authorization and x-rapidapi-key. also your function returns an object but excel cells need strings, try extracting specific fields from the json response first.
Looking at your code, there are several issues that need fixing. First, you’re mixing up GET and POST request patterns - you’re using GET but trying to send query parameters in the body with httpRequest.Send. For GET requests with RapidAPI, you should append the domain parameter directly to the URL like this: apiUrl = “https://jsonwhois.p.rapidapi.com/api/v1/whois/” & siteName, then use httpRequest.Send without any parameters.
Second issue is in your ProcessDomains sub - the Range(“B” & Rows.Count).End(xlUp).Offset(1, 0) will keep writing to the same cell instead of corresponding rows. You need to track the row number properly. Also, your FetchDomainInfo function isn’t returning a value correctly - you’re setting FetchDomainInfo to an object instead of a string value.
I had similar struggles when I first worked with APIs in VBA. The key is getting the HTTP request structure right and handling the JSON response properly. Make sure you have the JsonConverter module imported, and consider adding error handling for failed API calls since domain lookups can timeout or return errors.
Your main problem is with how you’re handling the row assignment in ProcessDomains. Using Range(“B” & Rows.Count).End(xlUp).Offset(1, 0) creates a dynamic reference that doesn’t align with your input rows. Instead, you should use the actual row counter. I’ve dealt with this exact scenario before and found that declaring a proper row variable works much better.
Also worth mentioning that your function structure needs adjustment. When FetchDomainInfo encounters an error or invalid response from the API, it will crash your entire loop. I learned this the hard way after processing hundreds of domains and having it fail halfway through. Consider wrapping the API call in error handling and returning a default value like “Error” or “Not Found” when the lookup fails.
One more thing - make sure you’re not hitting rate limits. Most RapidAPI plans have request limits per minute, and Excel VBA doesn’t handle throttling automatically. Adding a small delay between requests saved me from getting temporarily blocked.