Baselinker - download orders & products with API getOrders() using excell vba

1. Get a key

How to:

https://baselinker.com/pl-PL/pomoc/wiedza/api/

I will XXXXXXX as a example of key = api token

2. Create a macro with POST method

• I will use JSON converter in order to retreive data from https://github.com/VBA-tools/VBA-JSON
• Method description https://api.baselinker.com/index.php?method=getOrders
• Limit of the API request is 100 (recurring function is used to pass it)
• You need to include a reference to "Microsoft Scripting Runtime" in you VBA project

Sub PostAPI(ByRef httpr, ByVal ldate)

myurl = "https://api.baselinker.com/connector.php"

httpr.Open "POST", myurl, False

httpr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

params = "token=XXXXXXX&method=getOrders&parameters={" & Chr(34) & "date_from" & Chr(34) & ":+1" & ldate & "}"

httpr.send params

' Message box to uncomment in order to check error or result

' MsgBox (httpr.responseText)

End Sub

Sub Baselinker()

'

' PostREST Makro

'

Dim Json As Object

Dim httpRequest, ldate

Set httpRequest = CreateObject("msxml2.xmlhttp")

Sheets(1).Cells(1, 1).Value = "order_id"

Sheets(1).Cells(1, 2).Value = "date_confirmed"

Sheets(1).Cells(1, 3).Value = "delivery_address"

Sheets(1).Cells(1, 4).Value = "name"

Sheets(1).Cells(1, 5).Value = "price_brutto"

i = 2

o = 1

r = 1

While (r > 0)

    

        Call PostAPI(httpRequest, ldate)

        Set Json = JsonConverter.ParseJson(httpRequest.responseText)

        o = 1

        

        For Each Order In Json("orders")

        

         For Each Product In Order("products")

            Sheets(1).Cells(i, 1).Value = Order("order_id")

            ' Conversion of unix date format

            Sheets(1).Cells(i, 2).Value = DateAdd("s", Order("date_confirmed"), "1/1/1970 00:00:00")

            Sheets(1).Cells(i, 3).Value = Order("delivery_address")

            Sheets(1).Cells(i, 4).Value = Product("name")

            Sheets(1).Cells(i,5).Value = Product("price_brutto")

            

            ' save the last date

            ldate = Right(Order("date_confirmed"), 9)

            i = i + 1

            

            Next Product

        o = o + 1

    Next Order

    

    If o = 101 Then

        r = 1   

    Else

        r = 0

    End If

    

Wend

End Sub