Get information from API using VBA and Update the Retrieved information in the column

[RajG]

Dear Expert,

I hope all are doing well.

I have a requirement to retrieve the API data and the retrieved data will be displayed in an Excel cell. So, I successfully retrieved data from the API. But, I wasn't able to display the retrieved data in an Excel cell.

Can you please help me with this.

I have attached the screenshot for your reference.

I am using the below code to get the API data via VBA

code.

Private Sub CommandButton1_Click()

Dim JsonObject As Object
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String

Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://dummyjson.com/carts"
'strUrl = ws.[APIUrl]
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.setRequestHeader "Content-Type", "application/json"
'.setRequestHeader "Authorization", ws.[C7] & token
.Send
'Getting response from APIs
While objRequest.readyState <> 4
DoEvents
Wend
'API Response stored in var (strResponse)
strResponse = .responseText
End With
MsgBox (strResponse) 'Response will display in Popup box
'Range("P10").Value = strResponse 'Response will display in Sheet Column P10
'Debug.Print strResponse 'Response will display in Immediate window(VBA)

End Sub