VBA API call not retuning values

[yits05]

Hello,

I have the following VBA code which passes a POST request. When tested in the debugger, the API works fine, but for some reason the VBA does not return the response into the cell as it is supposed to. What I would like it to do is pass the content of cell F3 to the API, and return the response to Cell G3, F4 to G4, etc. Appreciate any help!

Public Sub XMLhttp_NLP()

    On Error Resume Next
    Dim Content As Long
    Dim Strresponse As Long
    Dim i As Long
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Sheet As Worksheet
    Set Sheet = Sheets(Sheet1)

For i = 3 To FinalRow
    Content = Range("F" & i)


    Const APIkey = "pretendapikey12345"
   
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")

    Dim rootURL As String, registrationEndpointURL As String
    Dim registration As String
    Dim text As String
    text = Content
   
    rootURL = "https://api.service.com/"
   
   
    With httpReq
        .Open "POST", rootURL, False
        .setRequestHeader "text=", text
        .setRequestHeader "x-textrazor-key:", APIkey
        .send ("extractors=entities,entailments&text=" & text)
        .responseText = Strresponse
       
        ThisWorkbook.Worksheets("Sheet1").Range("G" & i).Value = Strresponse
       
       
    End With

Next i

   
End Sub

 

[Dan_W]

I think that the problem is simply that these are in the wrong order:

    .responseText = Strresponse

should be:

Strresponse =  .responseText

See if that works.

 

[yits05]

I think that the problem is simply that these are in the wrong order:

    .responseText = Strresponse

should be:

Strresponse =  .responseText

See if that works.

Thank you - I tried but still nothing getting populated within the cells.

 

[Dan_W]

Hmm - odd. How are you certain that the API calls are actually working? Have you tried debugging it step-by-step? Do you see the variable strResponse being assigned the API return? Because if it is, you should see the results in cells G3, G4, G5 and so on on Sheet1 of the Workbook you've put that code in. The code is not in an Addin, is it?

 

[yits05]

Hmm - odd. How are you certain that the API calls are actually working? Have you tried debugging it step-by-step? Do you see the variable strResponse being assigned the API return? Because if it is, you should see the results in cells G3, G4, G5 and so on on Sheet1 of the Workbook you've put that code in. The code is not in an Addin, is it?

Hi, after debugging over and over, it suddenly started working. Everything had been assigned correctly, API return was showing up in debug mose, etc. Have no idea how!

 

[Dan_W]

Glad to hear it's working now. Still perplexed as to what happened, but best not to dwell on it!