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")
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
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