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

Answer 2

Thanks for your help. Here the corrected version:

Sub InsertPicFromURL()
    Dim myUrl As String         ' path of image
    Dim myPicture As Picture    ' embedded image
    Dim MyImage 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.
    Dim myFile As String
    Dim datim As String
    datim = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss") 'datetime to generate file
    myFile = Application.DefaultFilePath & "\SC" & datim & ".PNG"
    myUrl = "https://syncmediaapi-int.saphety.com/WCFSyncMediaWS.svc/rest/GetMediaContentByUrlId/6241bd8f-fbf0-4d53-844e-c8186aafeb05/"
    request.Open "GET", myUrl, False         ' Where to get image
    request.send                             ' Send the request for the webpage.
    MyImage = StrConv(request.responseBody, vbUnicode)  ' Get the webpage response text into response variable.
    Open myFile For Output As #1  'open file to save image
    Print #1, MyImage             'write to file
    Close #1                      'close file
    Set myPicture = ActiveSheet.Pictures.Insert(myFile) 'put image into cell
End Sub