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

What is the problem or issue? I think you forgot to ask a question. — BigBen, Jul 27 '20 at 16:42
Hi @BigBen. It is not showing the image. The value in **response** is "‰PNG IHDR ¨ j ñ Cµ ÿÿIDATxÚì œUÕµÆÏ¹m 30 ½Hï½# (MEE}ö®±F šX_^Lì-vM4‰½E lØP°Ð ”Þ{ †6 0å–óþç,îrsg #„³~fr¹÷œ½×Þ{okWÛq Ë _|ñåp Û >_|ñåp ø|ñÅ—ÃN|àóÅ _ ;ñÏ _|9ìÄ >_|ñå° ø|ñÅ—ÃN|àóÅ _ ;ñÏ _|9ìÄ >_|ñå° ø|ñÅ—ÃN|àóÅ _ ;ñÏ _|9ìÄ ¾x< " — Ricardo Cerceau, Jul 27 '20 at 16:48
Your GET request returns an actual image, not the path to an image. You need to save the image to local disk and then insert it from there. — Tim Williams, Jul 27 '20 at 16:58
Hi @TimWilliams, thanks for your help. I understood your idea but I don´t know how to do it. Is it possible to share any implementation idea? — Ricardo Cerceau, Jul 27 '20 at 19:34
Hi @TimWilliams, thanks for your help. I understood your idea and tried to solve. The result is not okay because the image is damage. — Ricardo Cerceau, Jul 27 '20 at 19:56
```VBA``` Dim myU As String Dim myP As Picture Dim MyI As String Dim req As New MSXML2.XMLHTTP60 Dim myF As String Dim datim As String datim=Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss") myF=Application.DefaultFilePath & "\SC" & datim & ".PNG" myU="https://syncmediaapi-int.saphety.com/WCFSyncMediaWS.svc/rest/GetMediaContentByUrlId/6241bd8f-fbf0-4d53-844e-c8186aafeb05/" req.Open "GET", myU, False req.send MyI=StrConv(req.responseBody, vbUnicode) Open myF For Output As #1 Write #1, MyI Close #1 Set myP = ActiveSheet.Pictures.Insert(myF) — Ricardo Cerceau, Jul 27 '20 at 20:06

score 0 · Accepted Answer · answered Jul 27 '20 at 20:08

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

score 0 · Answer 2 · edited Jul 28 '20 at 20:50

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

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

2 Answers2