Translate "-d" in VBA when doing an HTTP Request

user_19459521 0
Viewed 58

I try to get a list of actions from iAuditor filtered by "template_id" by sending an HTTP Request with VBA. For the moment I have this code that return the list of all actions:

Sub Get_Data()
Dim hReq As Object
Dim sht As Worksheet
Dim authKey As String
Dim response As String

authKey = "d8a0df7d7e1XXXXXXXXXXXXXXXXXXXXXXXXff3c765cf2fcf"

Set sht = Sheets(1)

Dim strUrl As String
    strUrl = "https://api.safetyculture.io/tasks/v1/actions/list"
Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "POST", strUrl, False
        .SetRequestHeader "Authorization", "Bearer " & authKey
        .SetRequestHeader "Content-Type", "application/json"
        .Send
    End With


    response = hReq.ResponseText
    'MsgBox Len(response)
    sht.Range("A1") = response
End Sub

Now I want to "translate" a filter to VBA but I have no idea how to do it.

Here is my API Documentation about the website : https://developer.safetyculture.io/#actions

So what i want to translate in VBA looks like this in HTTP code i guess :

curl -X POST "https://api.safetyculture.io/tasks/v1/actions/list" \
-H "Authorization: Bearer {api_token}" \
-d {
  "template_id": {
    "operator": 7,
    "value": ["fc2e53f6-4712-4ca5-b681-aba3ac954217"]
  }
}

I thank You a lot for any help you can offer me.

Regards, Gabin

filter http-headers vba xmlhttprequest
2 Answers
user_91183890

I've did it !!! :D

Here is my final code:

Sub Get_Data()
Dim hReq As Object
Dim sht As Worksheet
Dim authKey As String
Dim response As String
Dim response_objet As Object
Dim Body As String

authKey = "d8a0df7d7e19XXXXXXXXXXXXXXXXc65ec9eff3c765cf2fcf"

Body = "{ ""filters"": [ { ""template_id"": { ""value"": [""884b95df35104f4792a3c1fdfed63f0e""]}}]}"
Set sht = Sheets(1)
sht.Range("A3") = Body
Dim strUrl As String
    strUrl = "https://api.safetyculture.io/tasks/v1/actions/list"
Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "POST", strUrl, False
        .SetRequestHeader "Authorization", "Bearer " & authKey
        .SetRequestHeader "Content-Type", "application/json"
        .Send Body
    End With


    response = hReq.ResponseText
    'MsgBox Len(response)
    sht.Range("A1") = response
    Set response_objet = JsonConverter.ParseJson(response)
    For Each Item In response_objet
        a = a + 1
        Sheets("Biblio").Range("B" & a) = Item
    Next Item
End Sub

Huge thanks to @CodingWolf , he put me on the right direction.

Have a nice day internet. Gabin,

user_194595210
Related