Many ways to return API endpoint in various environments and coding method.

This is mine. Using VBA to return data into MS Excel spreadsheet. Standarised module for every call.

' 13/02/23
' Input: 'up_HTTP' String of URL API endpoint
' Input: 'ModName' calling Subroutine to include in 'User-Agent'
' Input: 'svalidate', additional checking if required
' Output: 'get_API' string contains endpoint data or error string
' Changed to include .status return value

Public Function get_data_API(ByRef up_http As Variant, ByRef ModName As Variant, Optional sValidate As Variant) As String

If IsMissing(sValidate) Then sValidate = ""

Dim xmlHttp
Dim iCount, iPCount As Integer
Dim sStatus As Variant

Set xmlHttp = CreateObject("msxml2.xmlhttp.6.0")

Dim UNameWindows As Variant
Dim sPath, sFileLog As Variant
UNameWindows = Environ("USERNAME")

sPath = ActiveWorkbook.Path
sFileLog = sPath & "\MPLog.txt"

booLog = ThisWorkbook.Worksheets("Control Panel").CheckBox9.Value

With xmlHttp
iCount = 0
iPCount = 0
Do
.Open "get", up_http, False
.setRequestHeader "User-Agent", ModName & ", username: " & UNameWindows & "; contact: xxxxxx@xxx.com"
.send
sStatus = .Status
'Debug.Print CSng(Trim(.Status))
Select Case sStatus

Case Is = 200
get_data_API = .responseText

Case Is = 301
'MsgBox "Wrong URL", vbCritical, "Incorrect"
get_data_API = "Error 301: Wrong URL " & up_http
iCount = 100 ' Not viable immediate return
booLog = True

Case Is = 304
'MsgBox "Data unchanged", vbCritical, "If-Modified-Since"
get_data_API = "Error 304: Data unchanged: " & up_http
iCount = iCount + 1 ' Try again

Case Is = 404
'MsgBox "No Data for URL" & xmlHttp, vbCritical, "Error 404"
get_data_API = "Error 404: No Data for URL: " & up_http
iCount = 100 ' Not viable immediate return
booLog = True

Case Is = 410
'MsgBox "No Data for URL will be available" & xmlHttp, vbCritical, "Error 410"
get_data_API = "Error 410: No Data for URL will be available: " & up_http
iCount = 100 ' Not viable immediate return
booLog = True

Case Is = 429
'MsgBox "Rate limit", vbCritical, "Error 429"
get_data_API = "Error 429: rate limit"
iCount = iCount + 1 ' Try again
booLog = True

Case Is = 502
'MsgBox "DB Overload", vbCritical, "Oh no!"
get_data_API = "Error 502: DB Overload"
iCount = iCount + 1 ' Try again
DoEvents
Application.Wait (Now + TimeValue("00:10:00"))

Case Else
get_data_API = "Error xxx: Unknown error"
iCount = iCount + 1 ' Try again
booLog = True

End Select

If booLog Then Call Log2File(sFileLog, sStatus, up_http)

If InStr(get_data_API, "Temporary rate limit exceeded") > 0 Then
iPCount = iPCount + 1
If InStr(Application.StatusBar, " Upload Paused") Then
Application.StatusBar = Left(Application.StatusBar, Len(Application.StatusBar) - 1) & iPCount
Else
Application.StatusBar = Application.StatusBar & " Upload Paused " & iPCount
End If
DoEvents
Application.Wait (Now + TimeValue("00:10:00"))
get_data_API = ""

End If
Loop While (get_data_API = "" And iCount < 100) Or InStr(LCase(get_data_API), "bad gateway") > 0
End With

Set xmlHttp = Nothing

End Function

ChessDev Hub

Super Admins

Admins

Coordinators