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