Excel VBA JSON POST Loop

Question

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

QHarr · Accepted Answer · 2018-08-26 17:38:28Z

1

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

edited Aug 26, 2018 at 17:38

answered Aug 26, 2018 at 17:09

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Tanner A. Over a year ago

That worked perfectly!! Exactly what I needed. Thank you so much for responding so quickly

Tanner A. Over a year ago

New Question Related to this:

Tanner A. Over a year ago

Sorry I added it to the original question

Tanner A. Over a year ago

Do i need to start a new question?

QHarr Over a year ago

Did you really want two loops or did you just want if col Y empty then take value from Z? And yes it should be a new question.

|

Collectives™ on Stack Overflow

Excel VBA JSON POST Loop

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related