Show image in Excel using VBA from rest is not showing any image

Question

I am trying to insert an image from a request and it is not showing any image. Here is my VBA code

Sub InsertPicFromURL()
    Dim myUrl As String                         ' path of pic
    Dim myPicture As Picture                    ' embedded pic
    Dim response As String                      ' create string to receive image in text format
    Dim request As New MSXML2.XMLHTTP60         ' Create the object that will make the webpage request.
    
    myUrl = "https://syncmediaapi-int.saphety.com/WCFSyncMediaWS.svc/rest/GetMediaContentByUrlId/6241bd8f-fbf0-4d53-844e-c8186aafeb05/"
    
    request.Open "GET", myUrl, False                      ' Where to go
    request.send                                          ' Send the request for the webpage.
    response = StrConv(request.responseBody, vbUnicode)   ' Get the webpage response text into response variable.
    Set myPicture = ActiveSheet.Pictures.Insert(response) 'put image into cell
End Sub

Answer 1

Something like this:

Sub InsertPicFromURL()
    Dim imgPath As String, myPicture
    
    imgPath = GetImagefile("https://syncmediaapi-int.saphety.com/WCFSyncMediaWS.svc/rest/GetMediaContentByUrlId/6241bd8f-fbf0-4d53-844e-c8186aafeb05/")
    Debug.Print imgPath
    Set myPicture = ActiveSheet.Pictures.Insert(imgPath)
    
End Sub

Function GetImagefile(url As String) As String
    Dim request As New MSXML2.XMLHTTP60, strm As Object, pth As String
    Set strm = CreateObject("ADODB.Stream")
    request.Open "GET", url, False
    request.send
    pth = TempPath()
    strm.Type = adTypeBinary
    strm.Open
    strm.Write request.responseBody
    strm.SaveToFile pth
    strm.Close
    GetImagefile = pth
End Function

Function TempPath() As String
    With CreateObject("scripting.filesystemobject")
        TempPath = .buildpath(.getspecialfolder(2), .gettempname())
    End With
End Function