CODEWITHSUNDEEP
jsonexcelvba
Tanner A.
Tanner A.

Reputation: 65

Excel VBA PUT Json

How can I make this work now? I'm trying to get the value from Column Y (rng1) and also the value from Column Z (rng2)

    Dim xhr As Object, thisRequest As String, rng1 As Range, rng2 As Range

Set xhr = CreateObject("Microsoft.XMLHTTP")

With ThisWorkbook.Worksheets("Fulcrum Upload")
    For Each rng1 In .Range("Y3:Y" & .Cells(.Rows.Count, "Y").End(xlUp).Row).SpecialCells(xlVisible)
    For Each rng2 In .Range("Z3:Z" & .Cells(.Rows.Count, "Z").End(xlUp).Row).SpecialCells(xlVisible)
        If Not IsEmpty(rng1) Then
            With xhr
                .Open "PUT", "https://api.fulcrumapp.com/api/v2/records/" + rng2 + ".json", False
                .setRequestHeader "Accept", "application/json"
                .setRequestHeader "Content-type", "application/json"
                .setRequestHeader "X-ApiToken", "11111111"
                .send rng1.value
            End With
        End If
    Next
End With

Upvotes: 1

Views: 150

Answers (1)

QHarr
QHarr

Reputation: 84465

I think you want to use offset to grab the adjacent value and thereby only need one loop. And use & to concantenate string.

With ThisWorkbook.Worksheets("Fulcrum Upload")
    For Each rng1 In .Range("Y3:Y" & .Cells(.Rows.Count, "Y").End(xlUp).Row).SpecialCells(xlVisible)
            If Not IsEmpty(rng1) Then
                With xhr
                    .Open "PUT", "https://api.fulcrumapp.com/api/v2/records/" & rng1.Offset(, 1).Value & ".json", False
                    .setRequestHeader "Accept", "application/json"
                    .setRequestHeader "Content-type", "application/json"
                    .setRequestHeader "X-ApiToken", "11111111"
                    .send rng1.Value
                End With
            End If
        Next
    End With
End Sub

Upvotes: 1

Related Questions