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
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