VBA API call not retuning values

yits05

Board Regular
Joined
Jul 17, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
428
Office Version
  1. 365
Platform
  1. Windows
I think that the problem is simply that these are in the wrong order:
VBA Code:
    .responseText = Strresponse
should be:
VBA Code:
Strresponse =  .responseText
See if that works.
 
Solution

yits05

Board Regular
Joined
Jul 17, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
I think that the problem is simply that these are in the wrong order:
VBA Code:
    .responseText = Strresponse
should be:
VBA Code:
Strresponse =  .responseText
See if that works.
Thank you - I tried but still nothing getting populated within the cells.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
428
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 17, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jul 11, 2018
Messages
428
Office Version
  1. 365
Platform
  1. Windows
Glad to hear it's working now. Still perplexed as to what happened, but best not to dwell on it! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,863
Messages
5,638,742
Members
417,049
Latest member
baka416

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top