First of all, you need to enable the Developer tab. Click File --> Options --> Customize Ribbon and turn on Developer checkbox.

Next, Import JSON Parser into VBA

• Open your Excel file.

• Go to the VBA Editor (press Alt + F11).

• In the menu, click File --> Import File....

• Select the JsonConverter.bas file located in the downloaded repository

After importing, make sure that the module appears in the project tree.

Create Command Button ( ActiveX Control) in Developer tab.

The following VBA code is used to interact with a REST API hosted locally (on http://localhost) to retrieve inventory data and populate it into an Excel worksheet.

In this code, we create 4 columns: InventoryID, Description, CurySpecificPrice, Quantity On Hand. We extract the Stock Item and fill in the 4 fields.

Private Sub CommandButton1_Click()

    Dim http As Object

    Dim url As String

    Dim requestBody As String

    Dim rawJSON As String

    Dim json As Object

    Dim ws As Worksheet

    Dim item As Object

    Dim warehouse As Object

    Dim i As Integer

    Dim cookies As String

    Dim totalQtyOnHand As Double

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Range("A2:Z" & ws.Rows.Count).ClearContents

    Set http = CreateObject("MSXML2.ServerXMLHTTP")

    On Error GoTo ErrorHandler

    url = "http://localhost/ExcelButtons/entity/auth/login"

    requestBody = "{""name"":""admin"",""password"":""123""}"

    http.Open "POST", url, False

    http.setRequestHeader "Content-Type", "application/json"

    http.Send requestBody

    If http.Status = 204 Then

        cookies = Split(http.getResponseHeader("Set-Cookie"), ";")(0)

        If cookies = "" Then

            MsgBox "Failed to retrieve cookies.", vbCritical

            Exit Sub

        End If

    Else

        MsgBox "Authentication failed. Status code: " & http.Status & vbCrLf & http.responseText, vbExclamation

        Exit Sub

    End If

    url = "http://localhost/ExcelButtons/entity/Default/24.200.001/StockItem?$expand=WarehouseDetails&$select=InventoryID,CurySpecificPrice,Description,WarehouseDetails/WarehouseID,WarehouseDetails/QtyOnHand"

    http.Open "GET", url, False

    http.setRequestHeader "Accept", "application/json"

    http.setRequestHeader "Content-Type", "application/json"

    http.setRequestHeader "Cookie", cookies

    http.Send

    If http.Status = 200 Then

        rawJSON = http.responseText

        Debug.Print rawJSON

        Set json = JsonConverter.ParseJson(rawJSON)

        ws.Cells(1, 1).Value = "InventoryID"

        ws.Cells(1, 2).Value = "Description"

        ws.Cells(1, 3).Value = "CurySpecificPrice"

        ws.Cells(1, 4).Value = "Quantity On Hand"

        i = 2

        For Each item In json

            totalQtyOnHand = 0

            If Not item("WarehouseDetails") Is Nothing Then

                For Each warehouse In item("WarehouseDetails")

                    If Not warehouse("QtyOnHand") Is Nothing Then

                        totalQtyOnHand = totalQtyOnHand + warehouse("QtyOnHand")("value")

                    End If

                Next warehouse

            End If

            If totalQtyOnHand > 0 Then

                If Not item("InventoryID") Is Nothing Then

                    ws.Cells(i, 1).Value = item("InventoryID")("value")

                End If

                If Not item("Description") Is Nothing Then

                    ws.Cells(i, 2).Value = item("Description")("value")

                End If

                If Not item("CurySpecificPrice") Is Nothing Then

                    ws.Cells(i, 3).Value = item("CurySpecificPrice")("value")

                End If

                ws.Cells(i, 4).Value = totalQtyOnHand

                i = i + 1

            End If

        Next item

        MsgBox "StockItems data successfully loaded!", vbInformation

    Else

        MsgBox "Failed to retrieve StockItems. Status code: " & http.Status & vbCrLf & http.responseText, vbExclamation

    End If

    url = "http://localhost/ExcelButtons/entity/auth/logout"

    http.Open "POST", url, False

    http.setRequestHeader "Cookie", cookies

    http.Send

    If http.Status = 204 Then

    Else

        MsgBox "Logout failed. Status code: " & http.Status & vbCrLf & http.responseText, vbExclamation

    End If

    Exit Sub

ErrorHandler:

    MsgBox "An error occurred: " & Err.Description, vbCritical

End Sub

You can make any request to the corresponding endpoint in Acumatica:

1.       by changing the url, name and password in Login request

url = "http://localhost/ExcelButtons/entity/auth/login"

requestBody = "{""name"":""admin"",""password"":""123""}"

http.Open "POST", url, False

http.setRequestHeader "Content-Type", "application/json"

http.Send requestBody

2.       changing url in Get request

url= "http://localhost/ExcelButtons/entity/Default/24.200.001/StockItem?$expand=WarehouseDetails&$select=InventoryID,CurySpecificPrice,Description,WarehouseDetails/WarehouseID,WarehouseDetails/QtyOnHand"