When importing API directly into Excel I get error “end of buffer reached”. Then I tried through VBA code below and i get MsgBox that data is empty.
I am truly lost right now because Postman aquires the data normaly via GET method but I do have to enter {“ID”:”AKTIVA”, “DNI”:”7″} into raw body to get anything.
I expected to be able to limit the data pulled through API into Excel for further analysis but so far I can only copy data from Postman into Excel manually which is unacceptable.
My code:
Sub FetchDataInBatches()
Dim url As String
Dim dni As String
Dim jsonResponse As String
Dim batchData As Object
Dim username As String
Dim password As String
Dim ws As Worksheet
Dim targetRow As Integer
url = "https://rk.sava.si/api/akt/web.php"
Set ws = ThisWorkbook.Worksheets("API_Sava")
username = "#####"
password = "#####"
dni = ws.Range("B2").Value
ws.Cells(3, 1).Value = "Obrat_ID"
ws.Cells(3, 2).Value = "Naziv"
ws.Cells(3, 3).Value = "Prostor_ID"
ws.Cells(3, 4).Value = "Datum"
ws.Cells(3, 5).Value = "Prihod"
ws.Cells(3, 6).Value = "Odhod"
ws.Cells(3, 7).Value = "Gostov"
targetRow = 4 ' Začnemo z vrstico 4, ker so prve 3 vrstice že uporabljene
jsonResponse = GetAPIData(url, dni, username, password)
If jsonResponse = "" Then
MsgBox "API response is empty. Check your URL and authentication."
Exit Sub
End If
Set batchData = JsonConverter.ParseJson(jsonResponse)
If batchData Is Nothing Or batchData.Count = 0 Then
MsgBox "No data returned from API or empty batch. Check your API endpoint."
Exit Sub
End If
ProcessBatchData batchData, targetRow
MsgBox "Data fetching completed."
End Sub
Function GetAPIData(url As String, dni As String, username As String, password As String) As String
Dim http As Object
Dim response As String
Dim authHeader As String
Dim jsonBody As String
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
authHeader = "Basic " & Base64Encode(username & ":" & password)
jsonBody = "{""DNI"":""" & dni & """}"
With http
.Open "GET", url, False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", authHeader
.Send (jsonBody)
response = .ResponseText
End With
GetAPIData = response
End Function
Function Base64Encode(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As Object
Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
Base64Encode = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Sub ProcessBatchData(batchData As Object, ByRef targetRow As Integer)
Dim ws As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Sheets("API_Sava") ' Nastavite ime delovnega lista
For i = 1 To batchData.Count
ws.Cells(targetRow, 1).Value = batchData(i)("Obrat_ID")
ws.Cells(targetRow, 2).Value = batchData(i)("Naziv")
ws.Cells(targetRow, 3).Value = batchData(i)("Prostor_ID")
ws.Cells(targetRow, 4).Value = batchData(i)("Datum")
ws.Cells(targetRow, 5).Value = batchData(i)("Prihod")
ws.Cells(targetRow, 6).Value = batchData(i)("Odhod")
ws.Cells(targetRow, 7).Value = batchData(i)("Gostov")
targetRow = targetRow + 1
Next i
End Sub