CODEWITHSUNDEEP
jsonexcelvbapost
Tanner A.
Tanner A.

Reputation: 65

Excel VBA JSON POST Loop

What i'm trying to accomplish is to go through a specific Table Column and for each visible row return the value of that cell. This cell contains a formula that creates a JSON from values in the table in other columns. Here's an example of the column i want to loop through:

Json Output

Inside this loop i'm going to use each cell value and POST the JSON, but it needs to loop through and post for each cell in that columnn. Here is the VBA code I'm using to POST a single cell without the loop:

Sub FulcrumUpload()

Dim xhr As Object, thisRequest As String, JSONvalue As String

Set xhr = CreateObject("Microsoft.XMLHTTP")
thisRequest = "https://api.fulcrumapp.com/api/v2/records.json"

xhr.Open "POST", thisRequest, False
xhr.setRequestHeader "Accept", "application/json"
xhr.setRequestHeader "Content-type", "application/json"
xhr.setRequestHeader "X-ApiToken", "11111111111111"

xhr.Send Sheets("Fulcrum Upload").Range("V3").value

End Sub

Upvotes: 0

Views: 253

Answers (1)

QHarr
QHarr

Reputation: 84465

Assuming your filtered data is in column V, from V3 onwards, perhaps something like:

Public Sub FulcrumUpload()
    Dim xhr As Object, thisRequest As String, rng As Range

    Set xhr = CreateObject("Microsoft.XMLHTTP")
    thisRequest = "https://api.fulcrumapp.com/api/v2/records.json"

    With ThisWorkbook.Worksheets("Fulcrum Upload")
        For Each rng In .Range("V3:V" & .Cells(.Rows.Count, "V").End(xlUp).Row).SpecialCells(xlVisible)
            If Not IsEmpty(rng) Then
                With xhr
                    .Open "POST", thisRequest, False
                    .setRequestHeader "Accept", "application/json"
                    .setRequestHeader "Content-type", "application/json"
                    .setRequestHeader "X-ApiToken", "11111111111111"
                    .send rng.Value
                End With
            End If
        Next
    End With
End Sub

Upvotes: 1

Related Questions